 package com.scalar.chiptrack.sales.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.alarms.Alarm;
import com.scalar.chiptrack.alarms.AlarmUser;
import com.scalar.chiptrack.alarms.Comment;
import com.scalar.chiptrack.alarms.dao.AlarmDAO;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.marketing.dao.MarketingDAO;
import com.scalar.chiptrack.operations.Inventory;
import com.scalar.chiptrack.operations.LotNumberQtyAndDateOut;
import com.scalar.chiptrack.operations.LotQuantity;
import com.scalar.chiptrack.operations.ReservedQuantity;
import com.scalar.chiptrack.sales.*;
import com.scalar.chiptrack.users.Functionality;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;




/**
 * Implementation of <strong>DAO</strong> business logic for
 * search with sales order
 *
 * @author Kesava Kumar Achanala
 * @version $Revision: 1.23 $ $Date: 2011/03/21 16:48:27 $


 *** Modifications done by Raj Vallamsetla ***
 *   All the searches are replaced by Sales order no instead of Sales Order Id, meaning critira is replaced by Sales order no

 */


public class SalesDAO
{

    public static final int BY_SCHEDULE_DATE = 0;
    public static final int BY_REQUESTED_DATE = 1;

    public static final int RESERVED_LOT_QUANTITY = 0;
    public static final int ENTIRE_LOT_QUANTITY = 1;



   // ----------------------------------------------------- Instance Variables


   /**
    * Default Constructor
    *
    */
   public SalesDAO()
   {

   }

   /*
    * get the search String
    *
    */
   private static String loadSearchString(String marketingPartNo, String customerId, int isSample)
                            throws ScalarException
   {
       String search_string = "";
       if(isSample == 0)
           search_string = " and So_Is_Sample = 0";
       else
           search_string = " and So_Is_Sample = 1";

       if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(marketingPartNo);
            // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(marketingPartNo);

       if((customerId != null) && (!"0".equals(customerId)))
           search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);

       return search_string + " ORDER BY SalesOrder_No";

   }

   // Added by Basha (08/09/2003)
   private static String loadSearchString(String salesOrderId, String customerId, String manufacturingPartNo, int isSample)
                            throws ScalarException
   {
       String search_string = "";
       if(isSample == 0)
           search_string = " and So_Is_Sample = 0";
       else
           search_string = " and So_Is_Sample = 1";

       if((salesOrderId != null) && (salesOrderId.trim().length() != 0))
            search_string = search_string + " and Sales_Order.SalesOrder_No like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

       //if((manufacturingPartNo != null) && (!manufacturingPartNo.trim().equals("0")) && (manufacturingPartNo.trim().length() > 0))
       if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);
            // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(manufacturingPartNo);

       if((customerId != null) && (!"0".equals(customerId) && (customerId.length() > 0)))
           search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);

       return search_string + " ORDER BY SalesOrder_No";

   }

    private static String loadSearchStringForPendingOrders(String salesOrderId, String customerId, String manufacturingPartNo, int isSample, int year, String customerPO,  User user, String endCustomer, String endUser)
                             throws ScalarException
    {
        String search_string = "";
        if(isSample == 0)
            search_string = " and So_Is_Sample = 0";
        else
            search_string = " and So_Is_Sample = 1";

        if((salesOrderId != null) && (salesOrderId.trim().length() != 0)&& !("0".equalsIgnoreCase(salesOrderId.trim())))
             search_string = search_string + " and SO.SalesOrder_No like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

        //if((manufacturingPartNo != null) && (!manufacturingPartNo.trim().equals("0")) && (manufacturingPartNo.trim().length() > 0))
        if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
             search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);
             // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(manufacturingPartNo);

        if((customerId != null) && (!"0".equals(customerId) && (customerId.length() > 0)))
        {
            search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);
        }
        else //All Customers
        {
            if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                search_string = search_string + " and Customer_ID IN (SELECT CUSTOMER_ID FROM Customer_SalesPerson_Relationship WHERE SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')) ";
            }
        }

        if(year != 0)
        {
            search_string = search_string + " and year(SO.So_Booking_Date) = " + year;
        }

        if((customerPO != null) && (customerPO.trim().length() != 0) && !("0".equalsIgnoreCase(customerPO.trim())))
          search_string = search_string + " and SO.SO_Customer_PO_Number like '"+ StringUtils.formatToHandleQuote(customerPO.trim()) +"%'";

        if(((endCustomer != null) && (endCustomer.trim().length() > 0) && !("0".equalsIgnoreCase(endCustomer))) )
        {
            search_string = search_string + " AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''" +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID ) " +
                    " END like '"+ StringUtils.formatToHandleQuote( endCustomer.trim() ) +"%'";
        }
        if(((endUser != null) && (endUser.trim().length() > 0) && !("0".equalsIgnoreCase(endUser))) )
        {
            search_string = search_string + " AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID = -1 ) THEN '' " +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID ) " +
                    " END like '"+ StringUtils.formatToHandleQuote( endUser.trim() ) +"%'";
        }

        return search_string + " ORDER BY SalesOrder_No";

    }



   // --------------------------------------------------------- Public Methods

   /**
    * Get the PendingOrder details..
    *
    * @return ArrayList (PendingOrders)
    */

   // Added by Basha (08/09/2003)
   // Added year parameter by Prabhanjan on 09 Mar 2004
   public static ArrayList loadPendingOrdersToApprove(String salesOrderId, String customerId, String manufacturingPartNo, int isSample, int year, String customerPO, String endCustomer, String endUser) throws ScalarException
   {
        if (salesOrderId == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;
        String searchString = null;

        try {

            searchString = loadSearchStringForPendingOrders(salesOrderId,customerId,manufacturingPartNo,isSample, year, customerPO, null, endCustomer, endUser);
            conn = DataSource.getConnection();
            String query = "SELECT DISTINCT SO.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) AS SO_Booking_Date,Customer_ID,(SELECT C_NAME FROM CUSTOMER WHERE Customer_ID=SO.Customer_ID ) AS CUST_NAME,SalesOrder_No, " +
                    " CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN '' " +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID ) " +
                    " END AS EndCustomer," +
                    " CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID = -1 ) THEN ''" +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID ) " +
                    " END AS EndUser " +
                    " FROM Sales_Order SO, Customer_Open_Order_LineItem COOL " +
                    " WHERE SO_Is_Pending=0 and SO.SalesOrder_Id=COOL.SalesOrder_ID" ;
            //pstmt = conn.prepareStatement (SqlQueryMappings.get( "sales.salesOrder.pending.loadPendingOrdersToApprove") + searchString );
            pstmt = conn.prepareStatement (query + searchString );

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(rset.getString("CUST_NAME") );
                orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setEndCustomerName( rset.getString("EndCustomer"));
                orders.setEndUserName( rset.getString("EndUser"));

                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                if(! ( ("APPROVED".equals(orders.getStatus().trim())) || ("SHIPPED".equals(orders.getStatus().trim())) ) )
                {
                    pendingList.add(orders);
                }
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not PendingList for purchaseOrder from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }
   // Added by Basha (08/09/2003)

   public static ArrayList loadPendingOrdersWithPurchaseOrder(String customerPO,int isSample) throws ScalarException
   {
        if (customerPO == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.pending.loadPendingOrdersWithSalesOrderId") + loadSearchString(null,null,isSample));
            pstmt.setString( 1, customerPO.trim() + "%" );

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));

                //SharatH on 6th Aug 03.
                if(! ( ("APPROVED".equals(orders.getStatus().trim())) || ("SHIPPED".equals(orders.getStatus().trim())) ) )
                {
                    pendingList.add(orders);
                }
                //pendingList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not PendingList for purchaseOrder from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadPendingOrdersWithoutDates(String customerNo, String marketingPartNo,int isSample)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.pending.loadPendingOrdersWithoutDates")+loadSearchString(marketingPartNo,customerNo,isSample));

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setSalesOrderNo(rset.getString("SalesOrder_No"));

                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));

               //Added by Sarada on 11th June
               //Do not display Approved orders

                if(! ( ("APPROVED".equals(orders.getStatus().trim())) || ("SHIPPED".equals(orders.getStatus().trim())) ) )
                {
                    pendingList.add(orders);
                }

              }//while

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not PendingList from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadPendingOrdersWithDates(String customerNo, String marketingPartNo,String fromDate, String toDate,int isSample) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.pending.loadPendingOrdersWithDates")+loadSearchString(marketingPartNo,customerNo,isSample));
            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                pendingList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not PendingList from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }


   public static ArrayList loadViewOrdersWithSalesOrderId(String customerPoNo,int isSample) throws ScalarException
   {
        if (customerPoNo == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.view.loadViewOrdersWithSalesOrderId") + loadSearchString(null,null,isSample));
            pstmt.setString (1, customerPoNo + "%");

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                pendingList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not ViewList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadViewOrdersWithoutDates(String customerNo, String marketingPartNo, int isSample, String status) throws ScalarException {
        String productId = "0";
        if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0)) {
            productId = Integer.toString(MarketingDAO.loadProductId(marketingPartNo));
        }


        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            pstmt =  conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.view.loadViewOrdersWithoutDates"));
            pstmt.setString (1, productId);
            pstmt.setString (2, customerNo);
            pstmt.setInt    (3, isSample);
            pstmt.setString (4, status);

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("Customer_PO_Number"));
                orders.setBookingDate(rset.getString("Booking_Date"));
                orders.setCustomerName(rset.getString("Customer_Name"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                //orders.setStatus(rset.getString("SO_Status"));
                pendingList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not View List for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadViewOrdersWithDates(String customerNo, String marketingPartNo,
                    String fromDate, String toDate, int isSample) throws ScalarException
   {
        String productId = null;

        if((customerNo == null) && (marketingPartNo == null)) {
            return null;
        }

        if ((customerNo == "0") || ("0".equals(customerNo)))
            customerNo = "";
        if((marketingPartNo == null) || (marketingPartNo == "") || ("".equals(marketingPartNo)))
            productId = "";
        else
            productId = Integer.toString(MarketingDAO.loadProductId(marketingPartNo));

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList viewList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.view.loadViewOrdersWithDates"));
            pstmt.setString (1, "%" + productId + "%");
            pstmt.setString (2, "%" + customerNo + "%");
            pstmt.setDate (3, StringUtils.getSQLDate(fromDate,null));
            pstmt.setDate (4, StringUtils.getSQLDate(toDate,null));

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                //orders.setStatus(rset.getString("SO_Status"));
                viewList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not VeiwList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return viewList;
   }


   public static ArrayList loadViewOrderLineItems(String salesOrderNo,String customerNo, String marketingPartNo, int isSample,
                                        String status, int year, String endCustomer, String endUser, String customerPO, User user ) throws ScalarException
   {
        String productId = "0";

       if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0)) {
            productId =  Integer.toString(MarketingDAO.loadProductIdByMfgPartNo(marketingPartNo));
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList viewList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            String query = "";
            boolean isAndRequired = false;
            if( ConfigManager.isEndUserPartNumbersAllowed() )
                query = SqlQueryMappings.get("sales.salesOrder.view.loadViewOrdersLineItemsWithSalesOrderNoByYearWithEndUserPartNumbers");
            else
                query = SqlQueryMappings.get("sales.salesOrder.view.loadViewOrdersLineItemsWithSalesOrderNoByYear");

            query = query + " Where 1 = 1 ";
            if(((salesOrderNo != null) && (salesOrderNo.trim().length() > 0) && !("0".equalsIgnoreCase(salesOrderNo))) )
            {
                query = query + " AND SalesOrder_No like '"+ StringUtils.formatToHandleQuote( salesOrderNo.trim() ) +"'";
            }
 
            if(((customerPO != null) && (customerPO.trim().length() > 0) && !("0".equalsIgnoreCase(customerPO)))  )
            {
                if( customerPO.indexOf('_') != -1 ){
                    customerPO = customerPO.replaceAll("_","#_");
                }
                if( customerPO.indexOf('%') != -1 ){
                    customerPO = customerPO.replaceAll("%","#%") ;
                }

                query = query + " AND Customer_PO_No like '"+ StringUtils.formatToHandleQuote( customerPO.trim() ) + "' ESCAPE '#'";
            }

            if(year!=0)
            {
                query = query + " AND year(Booking_Date) = " + year;
            }
            if(((endUser != null) && (endUser.trim().length() > 0) && !("0".equalsIgnoreCase(endUser))) )
            {
                query = query + " AND End_User like '"+ StringUtils.formatToHandleQuote( endUser.trim() ) +"'";
            }
            if ( "0".equals( customerNo) && user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                //query = query + " CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = " + user.getSalesPersonId() + " AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active'))";
                query = query + " AND CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active'))";
            }
            query = query + " ORDER BY SalesOrder_No,LineItem_No";
            //System.out.println("query build is  " + query );
            pstmt =  conn.prepareStatement(query);
            pstmt.setString (1, productId);
            pstmt.setString (2, customerNo);
            pstmt.setInt    (3, isSample);
            pstmt.setString (4, status);
            pstmt.setString(5, endCustomer);
            //pstmt.setString(6, endUser);

            rset = pstmt.executeQuery();
            LineItem item = null;

            while (rset.next()) {
                item = new LineItem();
                item.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                item.setSalesOrderNo(rset.getString("SalesOrder_No"));
                item.setCustomerName(rset.getString("Customer_Name"));
                item.setLineItemNo(rset.getInt("LineItem_NO"));
                item.setManufacturingPartNo(rset.getString("Part_Number"));
                item.setMarketingPartNo(rset.getString("mrkt_part_number"));
                item.setSalesHold(rset.getString("Sales_Hold"));
                item.setMarketingHold(rset.getString("Marketing_Hold"));
                item.setOperationsHold(rset.getString("Operations_Hold"));
                item.setFinanceHold(rset.getString("Finance_Hold"));
                item.setBookingDate(rset.getString("Booking_Date"));
                item.setRequestDate(rset.getString("Req_Date"));
                item.setScheduleDate(rset.getString("Schedule_Date"));
                item.setRevisedDate(rset.getString("Revised_Date"));
                item.setQuantity(rset.getInt("Req_Qty"));
                item.setShippedQty(rset.getInt("Shipped_Qty"));
                item.setStatus(rset.getString("Status"));
                item.setLineItemDescription(rset.getString("COOL_Description"));
		        item.setCustomerPartNo(rset.getString("Customer_PartNumber"));
                item.setCustomerPO ( rset.getString ( "Customer_PO_No" ) ) ;
                item.setEndUser( rset.getString("End_User") );
                item.setEndCustomer( rset.getString("End_Customer") );
                item.setShipmentNo(rset.getInt("Shipment_No"));
                item.setOrderType( rset.getString("Order_Type"));
                viewList.add(item);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not View List for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return viewList;
   }



   // Added by Balaram on Dec 10, 2002
   // Loads all missed line items
   public static ArrayList loadAllShippableLineItemsByDate (String date) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList shippableLineItemsList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllShippableLineItemsByDate"));
            pstmt.setString (1, date);
            pstmt.setString (2, date);

            rset = pstmt.executeQuery();
            LineItem lineItem = null;

            while (rset.next()) {

                lineItem = new LineItem (rset.getString ("P_Marketing_Part_Number"),
                                          rset.getInt ("SalesOrder_ID"),
                                          rset.getInt ("COOL_Line_Item_No"),
                                          rset.getInt ("COOL_Qty"),
                                          rset.getFloat ("COOL_Unit_Cost"),
                                          rset.getFloat ("COOL_Amount"),
                                          rset.getString ("COOL_Requested_Date"),
                                          rset.getString ("COOL_Booking_Date"),
                                          rset.getString ("COOL_Schedule_Date"),
                                          rset.getString ("COOL_Revised_Date"),
                                          rset.getString ("COOL_Status"));

                lineItem.setSalesOrderNo (rset.getString ("SalesOrder_No"));
                shippableLineItemsList.add (lineItem);
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not all shippable line items on " + date, sqle);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }


        return shippableLineItemsList;
   }



   // Added by Balaram on Dec 10, 2002
   // Loads all missed line items
   public static ArrayList loadAllMissedLineItems() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList missedLineItemsList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadAllMissedLineItems"));

            rset = pstmt.executeQuery();
            LineItem lineItem = null;

            while (rset.next()) {

                lineItem = new LineItem (rset.getString ("P_Marketing_Part_Number"),
                                          rset.getInt ("SalesOrder_ID"),
                                          rset.getInt ("COOL_Line_Item_No"),
                                          rset.getInt ("COOL_Qty"),
                                          rset.getFloat ("COOL_Unit_Cost"),
                                          rset.getFloat ("COOL_Amount"),
                                          rset.getString ("COOL_Requested_Date"),
                                          rset.getString ("COOL_Booking_Date"),
                                          rset.getString ("COOL_Schedule_Date"),
                                          rset.getString ("COOL_Revised_Date"),
                                          rset.getString ("COOL_Status"));

                lineItem.setSalesOrderNo (rset.getString ("SalesOrder_No"));
                missedLineItemsList.add (lineItem);
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not all missed line items from database", sqle);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }


        return missedLineItemsList;
   }

    // Added by Basha (08/09/2003)
    public static ArrayList loadMissedOrdersWithAll(String salesOrderId,String customerId,String manufacturingPartNo, int isSample,String byDate, int year, String customerPO) throws ScalarException
    {
            if (salesOrderId == null) {
                return null;
            }

            Connection conn = null;
            PreparedStatement pstmt = null;
            ArrayList pendingList = new ArrayList();
            ResultSet rset = null;
            String searchString = null;

            try
            {
                searchString = loadSearchString ( salesOrderId, customerId, manufacturingPartNo, isSample, byDate, year, customerPO );
                conn = DataSource.getConnection();
                //SharatH on 16th July 03.
                /*
                System.out.println("---------------------------------------------------------------------------------");
                System.out.println("MissedOrderSearchQuery:   "+(SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithAll")
                                +loadSearchString(salesOrderId, customerId, manufacturingPartNo, isSample, byDate)));
                System.out.println("---------------------------------------------------------------------------------");
                System.out.println();
                */

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithAll") + searchString );
                rset = pstmt.executeQuery();
                Orders orders = null;

                while (rset.next())
                {
                    orders = new Orders();
                    orders.setSalesOrderId (rset.getInt("SalesOrder_ID"));
                    orders.setCustomerPO ( rset.getString("SO_Customer_PO_Number"));
                    orders.setBookingDate(rset.getString("SO_Booking_Date"));
                    orders.setCustomerName( CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                    orders.setStatus( loadStatus(null,orders.getSalesOrderId() ));
                    orders.setSalesOrderNo( rset.getString("SalesOrder_No") );
                    //SharatH on 6th Aug 03.
                    if(! ("SHIPPED".equals(orders.getStatus())))
                    {
                        pendingList.add(orders);
                    }
                    //pendingList.add(orders);
                }
            }
            catch (Exception sqle) {
                sqle.printStackTrace();
                throw new ScalarException ("Could not Missedlist for salesOrderId from database", sqle);
            }
            finally {
                if (rset != null) {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }

            return pendingList;
     }
    // Added by Basha (08/09/2003)

    // SharatH on 16th July 03.
    /* Commented by Basha (08/11/2003)
    public static ArrayList loadMissedOrdersWithSalesOrderId(String customerPoNo,int isSample,String byDate) throws ScalarException
    {
        if (customerPoNo == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            //SharatH on 16th July 03.
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithSalesOrderId") + loadSearchString(null,null,isSample,byDate));
            pstmt.setString ( 1, customerPoNo.trim() + "%" );

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                //SharatH on 6th Aug 03.
                if(! (orders.getStatus().equals("SHIPPED")))
                {
                    pendingList.add(orders);
			    }
                //pendingList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Missedlist for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }
   Commented by Basha (08/11/2003) */

   public static ArrayList loadMissedOrdersWithoutDates(String customerNo, String marketingPartNo, String byDate,int isSample)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList missedList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithoutDates"));
            if((byDate != null) && ("ByRequestedDate".equals(byDate))) {

			   //commented by sarada on 22 august 2003 to move queries into sql query mappings

                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Requested_Date < CURRENT_TIMESTAMP) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
                      pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithoutDatesByRequestedDate")+ loadSearchString(marketingPartNo,customerNo,isSample));
            } else if((byDate != null) && ("ByScheduleDate".equals(byDate))) {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Schedule_Date < CURRENT_TIMESTAMP) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
				      pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithoutDatesByScheduleDate")+ loadSearchString(marketingPartNo,customerNo,isSample));
            } else if((byDate != null) && ("ByRevisedDate".equals(byDate))) {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Revised_Date < CURRENT_TIMESTAMP) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
                      pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithoutDatesByRevisedDate")+ loadSearchString(marketingPartNo,customerNo,isSample));
            } else  {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Schedule_Date < CURRENT_TIMESTAMP OR COOL_Revised_Date < CURRENT_TIMESTAMP) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
				       pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithoutDatesByThreeDates")+ loadSearchString(marketingPartNo,customerNo,isSample));
            }

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));

            //Added if condition by Sarada on 13th June,need not display Shipped salesOrder

                if(! ("SHIPPED".equals(orders.getStatus())))
                {
                    missedList.add(orders);
			    }
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not MissedList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return missedList;
   }

   public static ArrayList loadMissedOrdersWithDates(String customerNo, String marketingPartNo,
                    String fromDate, String toDate, String byDate,int isSample) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList missedList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            if((byDate != null) && ("ByRequestedDate".equals(byDate))) {

				//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Requested_Date < CURRENT_TIMESTAMP) and (COOL_Requested_Date between ? and ?) and Product_Id LIKE ? and Customer_Id LIKE ? and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
				       pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithDatesByRequestedDate")+  loadSearchString(marketingPartNo,customerNo,isSample));
            } else if((byDate != null) && ("ByScheduleDate".equals(byDate))) {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Schedule_Date < CURRENT_TIMESTAMP) and (COOL_Schedule_Date between ? and ?) and Product_Id LIKE ? and Customer_Id LIKE ? and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
				      pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithDatesByScheduleDate")+  loadSearchString(marketingPartNo,customerNo,isSample));

            } else if((byDate != null) && ("ByRevisedDate".equals(byDate))) {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Revised_Date < CURRENT_TIMESTAMP) and (COOL_Revised_Date between ? and ?) and Product_Id LIKE ? and Customer_Id LIKE ? and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
 			          pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithDatesByRevisedDate")+  loadSearchString(marketingPartNo,customerNo,isSample));

            } else {
                   // pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE COOL_Status <> 'SHIPPED' and (COOL_Schedule_Date < CURRENT_TIMESTAMP OR COOL_Revised_Date < CURRENT_TIMESTAMP) and (SO_Booking_Date between ? and ?) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID" + loadSearchString(marketingPartNo,customerNo,isSample));
					 pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.missed.loadMissedOrdersWithDatesByThreeDate")+  loadSearchString(marketingPartNo,customerNo,isSample));

            }
            //pstmt = conn.prepareStatement("SELECT DISTINCT Sales_Order.SalesOrder_ID,convert(varchar,SO_Booking_Date,101) SO_Booking_date, Customer_ID FROM Customer_Open_Order_LineItem, Sales_Order WHERE (COOL_Schedule_Date < CURRENT_TIMESTAMP OR COOL_Revised_Date < CURRENT_TIMESTAMP) and Product_Id LIKE ? and Customer_Id LIKE ? and (SO_Booking_Date between ? and ?) and Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID");
            pstmt.setDate (1, StringUtils.getSQLDate(fromDate,null));
            pstmt.setDate (2, StringUtils.getSQLDate(toDate,null));

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                missedList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not MissedList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return missedList;
   }


   // Added by Basha (08/09/2003)
   public static ArrayList loadCancelledOrders(String salesOrderId, String customerId, String manufacturingPartNo, int isSample, int year, String customerPO, User user) throws ScalarException
   {
        if (salesOrderId == null)
        {
            return null;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList cancelledList = new ArrayList();
        ResultSet rset = null;
        String searchString = null;
        try {

            searchString = loadSearchStringForPendingOrders(salesOrderId, customerId, manufacturingPartNo, isSample, year, customerPO, user, "0", "0");
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.cancelled.loadCancelledOrdersWithAll") + searchString );
            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(rset.getString("CUST_NAME"));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                cancelledList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not CancelledList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cancelledList;
   }
   // Added by Basha (08/09/2003)

   public static ArrayList loadCancelledOrdersWithSalesOrderId(String customerPoNo,int isSample) throws ScalarException
   {
        if (customerPoNo == null) {
            return null;
        }


        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList cancelledList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.cancelled.loadCancelledOrdersWithSalesOrderId")+loadSearchString(null,null,isSample));
            pstmt.setString ( 1, customerPoNo.trim() + "%" );

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                cancelledList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not CancelledList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cancelledList;
   }

   public static ArrayList loadCancelledOrdersWithoutDates(String customerNo, String marketingPartNo, int isSample)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList cancelledList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.cancelled.loadCancelledOrdersWithoutDates")+loadSearchString(marketingPartNo,customerNo,isSample));

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                cancelledList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not cancelledList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cancelledList;
   }

   public static ArrayList loadCancelledOrdersWithDates(String customerNo, String marketingPartNo,
                    String fromDate, String toDate,int isSample) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList cancelledList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.cancelled.loadCancelledOrdersWithDates")+loadSearchString(marketingPartNo,customerNo,isSample));
            pstmt.setDate (1, StringUtils.getSQLDate(fromDate,null));
            pstmt.setDate (2, StringUtils.getSQLDate(toDate,null));

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                cancelledList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not CancelledList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cancelledList;
   }

    public static ArrayList loadOrdersForChangeOrder(String salesOrderId, String customerId, String customerPO, String manufacturingPartNo, String viewShippedOrders, int isSample, int year, User user, String endCustomer, String endUser) throws ScalarException
    {
         if (salesOrderId == null) {
             return null;
         }

         Connection conn = null;
         PreparedStatement pstmt = null;
         ArrayList ordersList = new ArrayList();
         ResultSet rset = null;
         String searchString = null;

         try 
		 {
             searchString = loadSearchStringForChangeOrder( salesOrderId, customerId, customerPO, manufacturingPartNo, isSample, year, user,endCustomer,endUser   );
             if ( user != null && user.getUserType() != Constants.USER_TYPE_CHIPTRAC )
             {
                 searchString = " and Customer_Open_Order_LineItem.cool_status = 'HOLD' " + searchString;
             }

             conn = DataSource.getConnection();
             String query = "SELECT DISTINCT SO.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) AS SO_Booking_Date,Customer_ID,(SELECT C_NAME FROM CUSTOMER WHERE Customer_ID=SO.Customer_ID ) AS CUST_NAME,SO_Status,SalesOrder_No," +
                     " CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN '' " +
                     " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID ) " +
                     " END AS EndCustomer," +
                     " CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID = -1 ) THEN '' " +
                     " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID ) " +
                     " END AS EndUser " +
                     " FROM Sales_Order SO ,Customer_Open_Order_LineItem COOL WHERE SO.SalesOrder_Id=COOL.SalesOrder_ID";

             if(viewShippedOrders == null)
             {
                 query = query + " and COOL.cool_status <> 'SHIPPED' ";
                 //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.change.loadOrdersToChange") + searchString );
             }
             /*else
             {
                 //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.change.loadOrdersToChangeShipped") + searchString );
             }*/

             pstmt = conn.prepareStatement (query + searchString );

             rset = pstmt.executeQuery();
             Orders orders = null;
             String status = null;

             while (rset.next()) {
                 orders = new Orders();
                 orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                 orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                 orders.setBookingDate(rset.getString("SO_Booking_Date"));
                 orders.setCustomerName(rset.getString("CUST_NAME") );
                 orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                 orders.setEndCustomerName( rset.getString("EndCustomer"));
                 orders.setEndUserName( rset.getString("EndUser"));
                 status = loadStatus(null,orders.getSalesOrderId() );

                 //System.out.println("ChangeSalesOrderSearch -- Status:"+status);

                 // Condition modified by Basha (10/13/2003) to view Shipped Orders too
                 if (Constants.SALESORDER_STATUS_SHIPPED.equalsIgnoreCase(status) && viewShippedOrders == null) {
                     continue;
                 }

                 orders.setStatus(status);
                 ordersList.add(orders);
             }
         }
         catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException ("Could not OrdersList from database", sqle);
         }
         finally {
             if (rset != null) {
                 try { rset.close(); } catch (SQLException ignore) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore) {}
             }
         }

         return ordersList;
    }
    /**
	 * @param salesOrderId
	 * @param customerId
	 * @param customerPO
	 * @param manufacturingPartNo
	 * @param isSample
	 * @param year
	 * @param user
	 * @return
	 */
	private static String loadSearchStringForChangeOrder(String salesOrderId, String customerId, String customerPO, String manufacturingPartNo, int isSample, int year, User user, String endCustomer, String endUser) throws ScalarException
	{
		String search_string = "";
		
        if( isSample == 0 )
            search_string = " and So_Is_Sample = 0";
        else
            search_string = " and So_Is_Sample = 1";

        if( ( salesOrderId != null ) && ( salesOrderId.trim().length() != 0 ) && !("0".equalsIgnoreCase(salesOrderId)) )
             search_string = search_string + " and SO.SalesOrder_No like '"+ StringUtils.formatToHandleQuote( salesOrderId.trim() ) +"%'";

        if( ( customerPO != null ) && ( customerPO.trim().length() != 0 ) && !("0".equalsIgnoreCase(customerPO)) )
            search_string = search_string + " and SO.SO_Customer_PO_Number like '"+ StringUtils.formatToHandleQuote( customerPO.trim() ) +"%'";

        if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
             search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);

        if((customerId != null) && (!"0".equals(customerId) && (customerId.length() > 0)))
        {
            search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);
        }
        else //All Customers
        {
            if ( user != null && user.getUserType() == Constants.USER_TYPE_SALES_PERSON )
            {
                //search_string = search_string + " and Customer_ID IN (SELECT CUSTOMER_ID FROM Customer_SalesPerson_Relationship WHERE SALESPERSON_ID = " + user.getSalesPersonId() + " AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')) ";
                search_string = search_string + " and Customer_ID IN (SELECT CUSTOMER_ID FROM Customer_SalesPerson_Relationship WHERE SALESPERSON_ID IN (SELECT SALESPERSON_ID FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(" + user.getSalesPersonId() + ")) AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active')) ";
            }
        }

        if(year != 0)
        {
            search_string = search_string + " and year(SO.So_Booking_Date) = " + year;
        }
        if(((endCustomer != null) && (endCustomer.trim().length() > 0) && !("0".equalsIgnoreCase(endCustomer))) )
        {
            search_string = search_string + " AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''" +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID ) " +
                    " END like '"+ StringUtils.formatToHandleQuote( endCustomer.trim() ) +"%'";
        }
        if(((endUser != null) && (endUser.trim().length() > 0) && !("0".equalsIgnoreCase(endUser))) )
        {
            search_string = search_string + " AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID = -1 ) THEN '' " +
                    " ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID ) " +
                    " END like '"+ StringUtils.formatToHandleQuote( endUser.trim() ) +"%'";
        }

        return search_string + " ORDER BY SalesOrder_No";
	}

	// Added By Basha (08/09/2003)

   public static ArrayList loadOrdersForChangeOrder(String salesOrderId ,int isSample) throws ScalarException
   {
        if (salesOrderId == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList ordersList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.change.loadOrdersWithSalesOrderId")+loadSearchString(null,null,isSample));
            pstmt.setString ( 1, salesOrderId.trim() + "%" );

            rset = pstmt.executeQuery();
            Orders orders = null;
            String status = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
				orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                //SharatH on 6th Aug 03.
                status = loadStatus(null,orders.getSalesOrderId());

                if (Constants.SALESORDER_STATUS_SHIPPED.equalsIgnoreCase(status)) {
                    continue;
                }
                orders.setStatus(status);
                //orders.setStatus(loadStatus(null,orders.getSalesOrderId()));
                //orders.setStatus(rset.getString("SO_Status"));
                ordersList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not OrdersList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return ordersList;
   }

   public static ArrayList loadOrdersForChangeOrder(String customerNo, String marketingPartNo, int isSample)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList ordersList = new ArrayList();
        ResultSet rset = null;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.change.loadOrdersWithoutSalesOrderId")+loadSearchString(marketingPartNo,customerNo,isSample));
            rset = pstmt.executeQuery();
            Orders orders = null;
            String status = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                orders.setBookingDate(rset.getString("SO_Booking_Date"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
		        orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                status = loadStatus(null,orders.getSalesOrderId());

                if (Constants.SALESORDER_STATUS_SHIPPED.equalsIgnoreCase(status)) {
                    continue;
                }

                orders.setStatus(status);
                //orders.setStatus(rset.getString("SO_Status"));
                ordersList.add(orders);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not OrderList for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return ordersList;
   }


   public static String loadStatus(DBTransaction dbTransaction, int salesOrderId) throws ScalarException
   {

        Connection conn = null;
        CallableStatement cstmt = null; // added by Basha (08/26/2003)
        ResultSet rset = null;
        String status = "";

        try {

             // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

            // Begin : Added by Basha (08/26/2003)
            cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.getSalesOrderStatusForSearch"));
            cstmt.setInt (1, salesOrderId);
            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);


            //System.out.println("Varchar:"+java.sql.Types.VARCHAR);

            //System.out.println("java.sql.Types.VARCHAR:"+java.sql.Types.VARCHAR);

            cstmt.execute();

            status = cstmt.getString(2);
            // End : Added by Basha (08/26/2003)

            //System.out.println("Load Status String:"+status);

            /* Commented by Basha (08/26/2003)
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getHoldTypes"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            while (rset.next()) {
                status = status + " "+ (rset.getString("Hold_By_Group"));
            }

            if(status.length() == 0)
            {
                pstmt.close();
                pstmt = null;
                rset.close();
                rset = null;

                  //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

                //pstmt = conn.prepareStatement ("SELECT SO_Status From Sales_Order Where SalesOrder_ID = ?");
				  pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getStatus"));
                pstmt.setInt (1, salesOrderId);

                rset = pstmt.executeQuery();

                if (rset.next() == false)
                {
                   return null;
                }
                else
                {
                    status = rset.getString("SO_Status");
                    if((status != null) && (status.equals("APPROVED")))
                    {
                        pstmt.close();
                        pstmt = null;
                        rset.close();
                        rset = null;
                         //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

                        //pstmt = conn.prepareStatement ("SELECT CST_Status FROM Customer_Shipping_Table WHERE SalesOrder_ID = ?");
						 pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getStatusFromCustomerShippingTable"));
                        pstmt.setInt (1,salesOrderId);

                        rset = pstmt.executeQuery();

                        if (rset.next() == false)
                        {
                            return status;
                        }
                        else
                        {
                            status = rset.getString("CST_Status");
                        }
                    }
                }
            }
            else
            {
                status = "Hold By - " + status;
            } */
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not hold types for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        //Reset to null if no holds found

        if(status != null)
        {
            //System.out.println("Status is Null in SalesDAO");
            if(status.length() == 0)
                status = null;
        }

        return status;
   }

   public static String loadOrderStatus(DBTransaction dbTransaction, int salesOrderId) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String status = "";

        try {

             // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getHoldTypes"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            while (rset.next()) {
                status = status + " "+ (rset.getString("Hold_By_Group"));
            }

            if(status.length() != 0) {
                status = "Hold By - " + status;
            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not hold types for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return status;
   }


   public static HashMap loadHoldsForOrder(int salesOrderId) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap holds = new HashMap();

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadHoldsForOrder"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            while (rset.next()) {
                holds.put(rset.getString("Hold_Type"), rset.getString("User_ID"));

            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not hold types for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return holds;
   }

   /**
    * Update Hold Table
    * @param salesOrderId
    * @param approved
    * @ return isApprovedOrder
    */
   public static void updateHoldTable(DBTransaction dbTransaction,
                int salesOrderId, boolean approved, User user) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }


         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateHoldTableForApprover"));
         if(approved)
            pstmt.setInt (1, 0);
         else
            pstmt.setInt (1, 1);

         pstmt.setString(2, user.getUserId());
         pstmt.setInt (3, salesOrderId);
         pstmt.setString (4, user.getGroupName());

         pstmt.executeUpdate();

         success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

   }

   /**
    * Update the Alarm
    * @param comments
    * @param salesOrderId
    * @param isApproved
    */
   public static void updateAlarm(String comments, int salesOrderId, boolean isApproved, User user)
                        throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        Alarm oldAlarm = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAlramId"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            if (rset.next() == false) {
                //No Alarm Id found.

            } else {

                int alarmId = rset.getInt("Alarm_ID");

                //Load all the existing alarm
                oldAlarm = AlarmDAO.loadAlarmById (alarmId);

                //Set the user response according to the approve/reject
                //Added by Rohan on August 26th 2003 for throwing an Exception when the alarm could not retrieved
                if(oldAlarm==null)
                        throw new ScalarException ("Could not get alarmId for the salesOrderId " + salesOrderId + " from database");

                if(isApproved)
                    oldAlarm.setUserResponse(
                        user.getUserId(), Alarm.ALARM_RESPONSE_ACCEPT, comments);
                else
                    oldAlarm.setUserResponse(
                        user.getUserId(), Alarm.ALARM_RESPONSE_REJECT, comments);

                oldAlarm.setLastUpdatedBy(user.getUserId());
                //this is added because when approver approvs also, it is sending with originator name
                oldAlarm.setOriginator(new AlarmUser(user.getUserId(),user.getEmail()));
                // Added by Veeru on 21st September 2004 to send mails to Ids entered for Sales Order Notifications in Email Notification Setup Screen
                Iterator iterator = oldAlarm.getRecipients();
                AlarmUser to   = null;
                ArrayList recipientList =  new ArrayList();
                while ( iterator.hasNext() ) {
                    to = ( AlarmUser )iterator.next();
                    recipientList.add( to );
                }

                String user_id = null;
                int location   = 0;
                String[]  strArr = SalesOrderDAO.loadEmailListForSalesOrderNotification();
                if ( strArr != null )
                {
                    for (int i = 0; i < strArr.length; i++) {
                        location = strArr[i].indexOf("@");
                        if ( location > 0 )
                        {
                            user_id = strArr[ i ].substring(0, location );
                            to = new AlarmUser(user_id, strArr[ i ] );
                            recipientList.add(to);
                        }
                    }
                }
                oldAlarm.setRecipients ( recipientList );
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
       }
       //Notify all recipents
       if ( oldAlarm != null )
       {

           try
           {
               SalesOrder salesOrder = SalesOrderDAO.loadSalesOrder( salesOrderId + "" );
               if ( salesOrder.getSalesPerson() != null && salesOrder.getSalesPerson().length() > 0 )
               {
                   SalesPerson salesPerson = NewCustomerDAO.getSalesPersonDetails( null, salesOrder.getSalesPerson() );
                   if ( salesPerson != null && salesPerson.isSalesAckMail() )
                   {
                       if ( salesPerson.getAddress() != null && salesPerson.getAddress().getEmail() != null && salesPerson.getAddress().getEmail().trim().length() > 0 )
                       {
                           oldAlarm.addRecipient ( new AlarmUser( salesPerson.getSalesPerson (), salesPerson.getAddress().getEmail().trim() ) );
                       }
                   }
               }
               oldAlarm.notifyAllRecipients(salesOrder.getHtmlView());
           }
           catch ( Exception e )
           {
               throw new ScalarException ( "Could not send notification mail" );
           }
       }
   }

    public static void updateAlarmAndSendMailWithChangeSummary( String comments, int salesOrderId, boolean isApproved, User user,String changeSummary ) throws ScalarException
	{
        Alarm oldAlarm          = null;
        int alarmId             = 0;
        AlarmUser to            = null;
        ArrayList approversList = null;
        ArrayList recipientList = new ArrayList();
        ArrayList list          = null;
        User userBean           = null;
        String salesOrderStatus = null;

        try
        {
            alarmId = getAlarmIdBySalesOrderId( null, salesOrderId );

            if( alarmId > 0 )
            {
                //Load all the existing alarm
                //Alarm oldAlarm = AlarmDAO.loadAlarmById ( alarmId );
                oldAlarm = AlarmDAO.loadSalesOrderAlarmById( null, alarmId );

                //Set the user response according to the approve/reject
                if( oldAlarm == null )
                {
                    throw new ScalarException ("Could not get alarmId for the salesOrderId " + salesOrderId + " from database");
                }

                if( isApproved )
                {
                    oldAlarm.setUserResponse( user.getUserId(), Alarm.ALARM_RESPONSE_ACCEPT, comments );
                }
                else
                {
                    oldAlarm.setUserResponse( user.getUserId(), Alarm.ALARM_RESPONSE_REJECT, comments );
                }
                oldAlarm.setLastUpdatedBy( user.getUserId() );
                //this is added because when approver approvs also, it is sending with originator name
                oldAlarm.setOriginator( new AlarmUser( user.getUserId(), user.getEmail() ) );

                //ArrayList recipientList = AlarmDAO.loadRecipientsWithBoardKitChipAccess ( salesOrderId, oldAlarm );
                approversList = SalesOrderDAO.loadApproversList( null );

                for( int i = 0; i < approversList.size (); i++ )
                {
                    list = ( ArrayList ) approversList.get ( i );

                    for ( int j = 0; j < list.size (); j++ )
                    {
                        userBean = ( User ) list.get ( j );

                        //if ( ( userBean.getReceiveNotifications().equals ( "on" ) ) && ( userBean.getDisabled () == false ) )
                        if ( userBean.getSalesAckMail() && userBean.getDisabled () == false )
                        {
                            to = new AlarmUser ( userBean.getUserId (), userBean.getEmail () );
                            recipientList.add ( to );
                        }
                    }
                }

                oldAlarm.setRecipients ( recipientList );
                //oldAlarm.setRecipients ( AlarmDAO.loadRecipientsWithBoardKitChipAccess ( salesOrderId, oldAlarm ) );
                try
                {
                    SalesOrder salesOrder = SalesOrderDAO.loadSalesOrder( salesOrderId + "" );
                    if ( salesOrder.getSalesPerson() != null && salesOrder.getSalesPerson().length() > 0 )
                    {
                        SalesPerson salesPerson = NewCustomerDAO.getSalesPersonDetails( null, salesOrder.getSalesPerson() );
                        if ( salesPerson != null && salesPerson.isSalesAckMail() )
                        {
                            if ( salesPerson.getAddress() != null && salesPerson.getAddress().getEmail() != null && salesPerson.getAddress().getEmail().trim().length() > 0 )
                            {
                                oldAlarm.addRecipient ( new AlarmUser( salesPerson.getSalesPerson (), salesPerson.getAddress().getEmail().trim() ) );
                            }
                        }
                    }
                    salesOrderStatus = SalesDAO.loadStatus( null, salesOrder.getSalesOrderId() );
                    if( salesOrderStatus != null && salesOrderStatus.indexOf( Constants.SALESORDER_STATUS_HOLD ) >= 0  )
                    {
                        salesOrderStatus = Constants.SALESORDER_STATUS_HOLD;
                    }
                    if( salesOrder.getIsSample() )
                    {
                        oldAlarm.setSubject( "Sample Order Notification - SampleOrder No.: " + salesOrder.getSalesOrderNo() + " - Customer: " + salesOrder.getCustomerName() + " - Status: " + salesOrderStatus + " -- UPDATED");
                    }
                    else
                    {
                        oldAlarm.setSubject( "Sales Order Notification - SO No.: " + salesOrder.getSalesOrderNo() + " - Customer: " + salesOrder.getCustomerName() + " - Status: " + salesOrderStatus + " -- UPDATED" );
                    }
                    oldAlarm.notifyAllRecipients(salesOrder.getHtmlViewWithChangeSummary( changeSummary ));
                    //oldAlarm.notifyAllRecipients( SalesOrder.getHtmlViewWithChangeSummary( salesOrderId, changeSummary ) );
                }
                catch( Exception e ) { }
            }
            else
            {
                throw new ScalarException ("Could not get alarmId for the salesOrderId " + salesOrderId + " from database");
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for salesOrderId from database", sqle);
        }
        
    }


    public static int getAlarmIdBySalesOrderId( DBTransaction dbTransaction, int salesOrderId ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        int alarmId                 = -1; 

        try
        {

            if( dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get ("sales.salesOrder.loadAlramId" ) );
            pstmt.setInt( 1, salesOrderId );

            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                alarmId = rset.getInt( "Alarm_ID" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for salesOrderId from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return alarmId;
    }
   /*
    * Update LineItems with scheduledates and reviseddates
    * @param int salesOrderId
    * @param String[] lineItemNos
    * @param String[] scheduleDates
    * @param String[] revisedDates
    *
    */
   public static void updateLineItems(DBTransaction dbTransaction, int salesOrderId,
            String[] lineItemsNos, String[] scheduleDates, String[] revisedDates) throws
            ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

         boolean missedOrder = false;
         if(scheduleDates.length == 0) {
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateHoldTableForApprover"));
         }
         else {
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.updateHoldTableForApprover"));
             missedOrder = true;
         }

         pstmt.setInt (1, salesOrderId);

         for(int i=0; i<lineItemsNos.length; i++)
         {
            pstmt.setString (2, lineItemsNos[i]);
            if(missedOrder)
                pstmt.setDate (3, StringUtils.getSQLDate(revisedDates[i],null));
            else
                pstmt.setDate (3, StringUtils.getSQLDate(scheduleDates[i],null));

            pstmt.addBatch();
         }

         pstmt.executeBatch();

         success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
   }

   /*
    * Check for lineItesm, if all lineItems are scheduled OR Not
    * @param int SalesOrderId
    * @return boolean isApproved
    */
    public static boolean checkForLineItemHolds(int salesOrderId) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean isExists = true;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadHoldsForOrder"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            while (rset.next()) {
                return false;
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get schedule dates for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return isExists;

    }

   /*
    * Approve SalesOrder
    *
    */
    public static void approveSalesOrder(DBTransaction dbTransaction, int salesOrderId,
            String[] lineItemIds, String[] requestedDates,  String[] scheduleDates,String[] marketingHolds, String[] financeHolds,
                        String[] operationsHolds, String[] salesHolds, User user) throws ScalarException
    {

       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;
        String salesOrderNumber = "";

         //added by rohan on April 20th 2004 as these values are required for approving sales order in GP
          boolean[] isCancel = new boolean[ lineItemIds.length ]; //contains if the line item is cancelled
          int[] lineItemNumbers = new int[ lineItemIds.length ];  //contains line item numbers

          try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null)
         {
                conn = DataSource.getConnection();
         }
         else
         {
                conn = dbTransaction.getConnection();
         }



         //loading the sales order number.
         salesOrderNumber = SalesOrderDAO.getSalesOrderNumberByLineItemID( dbTransaction, Integer.parseInt( lineItemIds[ 0 ] ) );

         for(int i=0; i<lineItemIds.length; i++)
         {

     /*
            cstmt =  conn.prepareCall("{Call sp_approve_order_proc(?,?,?,?,?,?,??,?,?)}"); //This is new proc was added instead of approve_order_proc
            cstmt.setInt(1, Integer.parseInt(lineItemIds[i]));
             cstmt.setInt(2, salesOrderId);
            cstmt.setDate(3, StringUtils.getSQLDate(scheduleDates[i],null));
            cstmt.setString(4, user.getUserId());
            cstmt.setString(5, user.getUserId());
            cstmt.setString(6, user.getGroupName());
            cstmt.setInt (7, Integer.parseInt(marketingHolds[i]));
            cstm.setInt (8, Integer.parseInt(financeHolds[i]));
            cstmt.setInt (9, Integer.parseInt(operationsHolds[i]));
            cstmt.setInt (10, Integer.parseInt(salesHolds[i]));

            cstmt.execute(); */
            //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");

            if(marketingHolds != null)
            {
                if(cstmt != null) {
                 cstmt.close();
                 cstmt = null;
	             }
                  //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");

				cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.approveOrderLineitems"));

                if((marketingHolds[i] != null) && (marketingHolds[i].trim().length() != 0))
                {
                    cstmt.setString (1, marketingHolds[i]);
                    cstmt.setInt (2, 1);
                    cstmt.setString (3, "MARKETING");
	            } else {
                    cstmt.setString (1, "");
                    cstmt.setInt (2, 0); //if values is null then it means it is approved
                    cstmt.setString (3, "MARKETING");
                }
	            cstmt.setString (4, user.getUserId());
                cstmt.setInt (5, Integer.parseInt(lineItemIds[i]));
                cstmt.setInt (6, salesOrderId);
                cstmt.setDate(7, StringUtils.getSQLDate(scheduleDates[i],null));

	            cstmt.executeUpdate();
            }
            if(salesHolds != null)
            {
                if(cstmt != null) {
                 cstmt.close();
                 cstmt = null;
                }
                //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");
				cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.approveOrderLineitems"));

                if((salesHolds[i] != null)&& (salesHolds[i].trim().length() != 0))
                {
	                cstmt.setString (1, salesHolds[i]);
                    cstmt.setInt (2, 1);
                    cstmt.setString (3, "SALES");
                } else {
	                cstmt.setString (1, "");
                    cstmt.setInt (2, 0); //if values is null then it means it is approved
                    cstmt.setString (3, "SALES");
                }
                cstmt.setString (4, user.getUserId());
                cstmt.setInt (5, Integer.parseInt(lineItemIds[i]));
                cstmt.setInt (6, salesOrderId);
                cstmt.setDate(7, StringUtils.getSQLDate(scheduleDates[i],null));

                cstmt.executeUpdate();
            }
            if(financeHolds != null)
            {
				if(cstmt != null) {
                 cstmt.close();
                 cstmt = null;
                }
                //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");
				  cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.approveOrderLineitems"));

                if((financeHolds[i] != null)&& (financeHolds[i].trim().length() != 0))
                {
				    cstmt.setString (1, financeHolds[i]);
                    cstmt.setInt (2, 1);
                    cstmt.setString (3, "FINANCE");
                } else {

				    cstmt.setString (1, "");
                    cstmt.setInt (2, 0); //if values is null then it means it is approved
                    cstmt.setString (3, "FINANCE");
                }

                cstmt.setString (4, user.getUserId());
                cstmt.setInt (5, Integer.parseInt(lineItemIds[i]));
                cstmt.setInt (6, salesOrderId);
                cstmt.setDate(7, StringUtils.getSQLDate(scheduleDates[i],null));
                cstmt.executeUpdate();
            }
            if(operationsHolds != null)
            {
				 if(cstmt != null) {
                 cstmt.close();
                 cstmt = null;
                }
               // cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");
			      cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.approveOrderLineitems"));

                if((operationsHolds[i] != null)&& (operationsHolds[i].trim().length() != 0))
                {
				    cstmt.setString (1, operationsHolds[i]);

                    cstmt.setInt (2, 1);
                    cstmt.setString (3, "OPERATIONS");
                }
                else
                {
				    cstmt.setString (1, "");
                    cstmt.setInt (2, 0); //if values is null then it means it is approved
                    cstmt.setString (3, "OPERATIONS");
                }
                cstmt.setString (4, user.getUserId());
                cstmt.setInt (5, Integer.parseInt(lineItemIds[i]));
                cstmt.setInt (6, salesOrderId);
                /*String scheduleDate=null;
                if((operationsHolds[i] == null)|| (operationsHolds[i].trim().length() == 0))
                {
                    if (scheduleDates[i].trim().length() == 0 || scheduleDates[i] ==null)
                    {
                        scheduleDate= requestedDates[i];
                    }
                    else
                    {
                        scheduleDate = scheduleDates[i];
                    }
                }
                cstmt.setDate(7, StringUtils.getSQLDate(scheduleDate,null));*/
				cstmt.setDate(7, StringUtils.getSQLDate(scheduleDates[i],null));
                cstmt.executeUpdate();
            }
         }

         success = true;


       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();

       } finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }


    }

    public static void approveMissedOrder(String[] lineItemIds, String[] revisedDates,User user)
                        throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.updateMissedOrder"));

         for(int i=0; i<lineItemIds.length; i++)
         {
            pstmt.setDate(1,StringUtils.getSQLDate(revisedDates[i],null));
            pstmt.setString(2,user.getUserId());
            pstmt.setString(3,null);
            pstmt.setInt(4,Integer.parseInt(lineItemIds[i]));

            pstmt.addBatch();
         }

         pstmt.executeBatch();

         // Added by Basha (01/07/2004)
         //System.out.println("Revised date updation");

         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.updateCCORevisedDate"));

         for(int i=0; i<lineItemIds.length; i++)
         {
            pstmt.setDate(1,StringUtils.getSQLDate(revisedDates[i],null));
            pstmt.setString(2,user.getUserId());
            pstmt.setInt(3,Integer.parseInt(lineItemIds[i]));
            pstmt.setInt(4,Integer.parseInt(lineItemIds[i]));

            pstmt.addBatch();
         }

         pstmt.executeBatch();



         success = true;


       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           try {
                if (success == true)
                  conn.commit();
                else
                  conn.rollback();
           }
           catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

    }

    public static String loadSalesOrderLimit() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadSalesOrderLimit"));

         rset = pstmt.executeQuery();

         if(rset.next() == false)
         {
            return null;
         }
         else
         {
            return (Integer.toString(rset.getInt("Finance_Order_Limit")));
         }


       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return null;
    }

    public static String loadChangeOrderLimit() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadChangeOrderLimit"));

         rset = pstmt.executeQuery();

         if(rset.next() == false)
         {
            return null;
         }
         else
         {
            return (Integer.toString(rset.getInt("Finance_Change_Order_Limit")));
         }


       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return null;
    }


    public static ArrayList loadFinanceApprovers(DBTransaction dbTransaction) throws ScalarException
    {
        return (UserDAO.loadUsersWithModifyFunctionAccess(dbTransaction,Functionality.FINANCE_APPROVAL_RIGHTS));
    }

    public static ArrayList loadMarketingApprovers(DBTransaction dbTransaction) throws ScalarException
    {
        return (UserDAO.loadUsersWithModifyFunctionAccess(dbTransaction, Functionality.MARKETING_APPROVAL_RIGHTS));
    }

    public static ArrayList loadSalesApprovers(DBTransaction dbTransaction) throws ScalarException
    {
        return (UserDAO.loadUsersWithModifyFunctionAccess(dbTransaction, Functionality.SALES_APPROVAL_RIGHTS));
    }

    public static ArrayList loadOperationsApprovers(DBTransaction dbTransaction) throws ScalarException
    {
        return (UserDAO.loadUsersWithModifyFunctionAccess(dbTransaction, Functionality.OP_APPROVAL_RIGHTS));
    }


    public static ArrayList loadSalesOrderApprovers(DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList approvers = new ArrayList();

        try {

         if(dbTransaction == null)
            conn = DataSource.getConnection();
         else
            conn = dbTransaction.getConnection();
         //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

         //pstmt = conn.prepareStatement("select User_Table.user_id,email from User_Table, Function_Access_Table Where Function_Id = 56 and FAT_Modify = 1 and User_Table.user_id = Function_Access_Table.User_Id");
          pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadSalesOrderApprovers"));

         rset = pstmt.executeQuery();

         User user = null;

         while(rset.next())
         {
            user = new User();
            user.setUserId(rset.getString("User_ID"));
            user.setEmail(rset.getString("Email"));

            approvers.add(user);
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
            }

       }

       return approvers;
    }


   public static void saveOrderLimits(DBTransaction dbTransaction, String salesOrderLimit,
                    String changeOrderLimit) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

         cstmt =  conn.prepareCall("{Call approve_order_proc(?,?)}");
         cstmt.setInt (1, Integer.parseInt(salesOrderLimit));
         cstmt.setInt (2, Integer.parseInt(changeOrderLimit));

         success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
   }

   public static void saveApprovers(DBTransaction dbTransaction, String[] financeApprovers,
                    String[] marketingApprovers, String[] salesApprovers,
                    String[] operationsApprovers) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }

         //Delete all the approvers...
         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.deleteApprovers"));
         pstmt.executeUpdate();
         pstmt.close();
         pstmt = null;

         //Save the financeApprovers
         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.saveApprovers"));
         for(int i=0; i<financeApprovers.length; i++)
         {
             pstmt.setString(1,financeApprovers[i]);
             pstmt.setInt(2, Constants.SALESORDER_FINANCE_APPROVER);
             pstmt.addBatch();
         }
         pstmt.executeBatch();
         pstmt.close();
         pstmt = null;

         //Do the same thing for all the approvers are replace this with StoredProcedure...


         success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
   }

   public static HashMap loadLineItemHolds(int salesOrderId) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       HashMap holds = new HashMap();

       try {
            conn = DataSource.getConnection();

			//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
           // pstmt = conn.prepareStatement("Select COOL_Line_Item_No, Hold_User, Hold_Type, LineItem_Hold_Table.TimeStamp from LineItem_Hold_Table,Customer_Open_Order_LineItem Where LineItem_Hold_Table.SalesOrder_ID = ? and Is_Approved=1 and Customer_Open_Order_LineItem.LineItem_ID = LineItem_Hold_Table.LineItem_ID");
		     pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadLineItemHolds"));
            pstmt.setInt(1, salesOrderId);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                holds.put(("LineItem No - " + rs.getInt("COOL_Line_Item_No")+ " , Hold by   "+ (rs.getString("Hold_Type")) + " Group  ") , " by user name - " + (rs.getString("Hold_User") + " ,  on - " + rs.getDate("TimeStamp")));
            }

       } catch(Exception ex) {
           ex.printStackTrace();
           throw new ScalarException("Couldn't load lineItem holds for the salesOrder ");
       } finally {
            if (rs  != null) {
                try { rs.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return holds;
   }

   public static HashMap loadHoldCancelledLineItems(int salesOrderId) throws ScalarException
   {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       HashMap cancels = new HashMap();

       try {
            conn = DataSource.getConnection();

			//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
            // pstmt = conn.prepareStatement("Select COOL_Line_Item_No from Customer_Open_Order_LineItem Where COOL_Is_Cancel=0 and SalesOrder_ID = ?");
			 pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.loadHoldCancelledLineItems"));
            pstmt.setInt(1, salesOrderId);
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                cancels.put("LineItem No - " + rs.getInt("COOL_Line_Item_No"), " has been cancelled and needs approval");
            }

       } catch(Exception ex) {
           ex.printStackTrace();
           throw new ScalarException("Couldn't load lineItem holds for the salesOrder ");
       } finally {
            if (rs  != null) {
                try { rs.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return cancels;
   }

   public static ArrayList loadSalesOrders(String customerPO) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItemList = null;

        try {
         String sqlQry = "Select LineItem_ID,SO_Customer_PO_Number,Customer_ID,Sales_Order.SalesOrder_ID,COOL_Line_Item_No,"+
                         "COOL_Qty,COOL_Unit_Cost,COOL_Amount,COOL_Requested_Date,COOL_Booking_Date,COOL_Schedule_Date,"+
                         "COOL_Status,Product_ID,SalesOrder_No From Customer_Open_Order_LineItem,Sales_Order Where "+
                         "Sales_Order.SalesOrder_No like ? and Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID "+
                         "and Customer_Open_Order_LineItem.COOL_status NOT IN ('SHIPPED','APPROVED')";

         conn = DataSource.getConnection();
         //Original code kept in comments on 19 May 2003.
         //pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.holds.loadLineItemsWithPO"));
         //Bhaskar added this code on 19 May to filter out APPROVED and SHIPPED items.
         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.holds.loadLineItemsWithPOWithoutApprovalAndShipped"));
         pstmt.setString(1, customerPO + "%");
         rset = pstmt.executeQuery();

         lineItemList = new ArrayList();
         LineItem lineItem = null;

         while(rset.next())
         {
            lineItem = new LineItem();
            lineItem.setLineItemId(rset.getInt("LineItem_ID"));
            lineItem.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
		    lineItem.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
            lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
            lineItem.setLineItemNo(rset.getInt("COOL_Line_Item_No"));
            lineItem.setQuantity(rset.getInt("COOL_Qty"));
            lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
            lineItem.setAmount(rset.getFloat("COOL_Amount"));
            lineItem.setRequestDate(rset.getDate("COOL_Requested_Date")+"");
            lineItem.setBookingDate(rset.getDate("COOL_Booking_Date")+"");
            lineItem.setScheduleDate(rset.getDate("COOL_Schedule_Date")+"");
            lineItem.setStatus(rset.getString("COOL_Status"));
            lineItem.setProductId(rset.getInt("Product_ID"));
			lineItem.setSalesOrderNo(rset.getString("SalesOrder_No"));
            SalesOrderDAO.loadHoldsForLineItem(lineItem); //load holds
            SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);//load partnumbers
            lineItemList.add(lineItem);
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return lineItemList;
   }

    //Added by Sarada on 6th Aug 2003 to display lineitems Except Shipped and Partially Shippped  in Sales Holds Screen
    // Added parameter isSample by Veeru on 16th Sep 2004
    public static ArrayList loadSalesOrdersInSalesHolds(String customerPO, int isSample) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItemList = null;
       //System.out.println("Entered into loadSalesOrders");
        try {

         conn = DataSource.getConnection();
         //Original code kept in comments on 19 May 2003.
         //pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.holds.loadLineItemsWithPO"));
         //Bhaskar added this code on 19 May to filter out APPROVED and SHIPPED items.
         //System.out.println("isSample"+isSample);
         pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.holds.loadLineItemsWithPOWithoutShippedAndPartiallyShipped"));
	     pstmt.setString(1, customerPO+"%");
	     pstmt.setInt(2, isSample);
         rset = pstmt.executeQuery();

         lineItemList = new ArrayList();
         LineItem lineItem = null;

         while(rset.next())
         {
            lineItem = new LineItem();
            lineItem.setLineItemId(rset.getInt("LineItem_ID"));
            lineItem.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
	        lineItem.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
            lineItem.setCustomerId(rset.getInt("Customer_ID"));
            lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
            lineItem.setLineItemNo(rset.getInt("COOL_Line_Item_No"));
            lineItem.setQuantity(rset.getInt("COOL_Qty"));
            lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
            lineItem.setAmount(rset.getFloat("COOL_Amount"));
            lineItem.setRequestDate(StringUtils.formatDateToString(rset.getDate("COOL_Requested_Date"), null)+"");
            lineItem.setBookingDate(StringUtils.formatDateToString(rset.getDate("COOL_Booking_Date"),null)+"");
            lineItem.setScheduleDate(StringUtils.formatDateToString(rset.getDate("COOL_Schedule_Date"), null));
            lineItem.setRevisedDate(StringUtils.formatDateToString(rset.getDate("COOL_Revised_Date"), null));
            lineItem.setStatus(rset.getString("COOL_Status"));
            lineItem.setProductId(rset.getInt("Product_ID"));
	        lineItem.setSalesOrderNo(rset.getString("SalesOrder_No"));
	        // System.out.println("cust part no in first "+rset.getString("Customer_PartNumber"));
	        lineItem.setCustomerPartNo(rset.getString("Customer_PartNumber"));//Added by Sarada 0n 2oth Jan to get customer PArtNo
            lineItem.setOdmId( rset.getInt("SO_ODM_Id") ); //Addded by varma to fetch the Customer Part Number with combination of ODM and Customer
	        String finalStatus = (rset.getString("Final_COOL_Status")).trim();
            lineItem.setShippingTerms( rset.getString ( "SHIPPING_TERMS" ) );

	        SalesOrderDAO.loadHoldsForLineItem(lineItem); //load holds
            SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);//load partnumbers

            if( !( ("SHIPPED".equalsIgnoreCase(finalStatus)) || ("PARTIALLY SHIPPED".equalsIgnoreCase(finalStatus))	|| ("ISSUED".equalsIgnoreCase(finalStatus)) ) )
            {
                lineItemList.add(lineItem);
            }

         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return lineItemList;
   }

   public static ArrayList loadSalesOrders(String marketingPartNo, String customerId, boolean isHold) throws ScalarException
   {

       String search_string = "";
       if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(marketingPartNo);

       if((customerId != null) && (!"0".equals(customerId)))
           search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);


        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItemList = null;

        try {

         conn = DataSource.getConnection();
         if(isHold)
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadHoldLineItemsWithooutPO") + search_string);
         else
             //original code is in comments below
             //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPO") + search_string);
             //Bhaskar added this statement below on 19 May 2003.
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutApprovalAndShipped") + search_string);
			rset = pstmt.executeQuery();

         lineItemList = new ArrayList();
         LineItem lineItem = null;

         while(rset.next())
         {
            lineItem = new LineItem();
            lineItem.setLineItemId(rset.getInt("LineItem_ID"));
            lineItem.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
            lineItem.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
            lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
            lineItem.setLineItemNo(rset.getInt("COOL_Line_Item_No"));
            lineItem.setQuantity(rset.getInt("COOL_Qty"));
            lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
            lineItem.setAmount(rset.getFloat("COOL_Amount"));
            lineItem.setRequestDate(rset.getDate("COOL_Requested_Date")+"");
            lineItem.setBookingDate(rset.getDate("COOL_Booking_Date")+"");
            lineItem.setScheduleDate(rset.getDate("COOL_Schedule_Date")+"");
            lineItem.setStatus(rset.getString("COOL_Status"));
            lineItem.setProductId(rset.getInt("Product_ID"));
	    lineItem.setSalesOrderNo(rset.getString("SalesOrder_No"));
            SalesOrderDAO.loadHoldsForLineItem(lineItem); //load holds
            SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);//load partnumbers

            lineItemList.add(lineItem);
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return lineItemList;
   }

   //Added by Sarada on 6th Aug 2003 to display lineitems Except Shipped and Partially Shippped  in Sales Holds Screen
   // Added parameter isSample by Veeru on 16th Sep 2004
   public static ArrayList loadSalesOrdersInSalesHolds(String marketingPartNo, String customerId, boolean isHold, String year, int isSample) throws ScalarException
   {

       String search_string = "";
       if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
            search_string = " Product_Id = " + MarketingDAO.loadProductIdByMfgPartNo(marketingPartNo);

       if((customerId != null) && (!"0".equals(customerId)))
       {
            if("".equals(search_string))
                search_string = " Customer_ID = " + Integer.parseInt(customerId);
           else
                search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);
       }
       int bookingYear = 0;
       if ( (year != null ) && ( year.trim ().length () != 0 ))
       {
           try
           {
               bookingYear= Integer.parseInt ( year);
           }
           catch ( NumberFormatException nfe )
           {
               bookingYear = 0;
           }
       }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItemList = null;

        try {

         conn = DataSource.getConnection();
         if(isHold)
    	 {
             if ( bookingYear != 0 )
             {
                 if ( "".equals ( search_string ) )
                 {
                     search_string = " year(Sales_Order.SO_Booking_Date) = " + bookingYear;
                 }
                 else
                 {
                     search_string = search_string + " and year(Sales_Order.SO_Booking_Date) = " + bookingYear;
                 }
             }

            if("".equals(search_string))
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadHoldLineItemsWithooutPO"));
            else
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadHoldLineItemsWithooutPO") + " and " + search_string);
	     }
         else
         {
             if ( bookingYear != 0 )
             {
                 if ( "".equals ( search_string ) )
                 {
                     search_string = " year(SO_Booking_Date) = " + bookingYear;
                 }
                 else
                 {
                     search_string = search_string + " and year(SO_Booking_Date) = " + bookingYear;
                 }
             }
            if("".equals(search_string))
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutShippedAndPartiallyShipped") );
            else
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutShippedAndPartiallyShipped") + " where " + search_string);
            pstmt.setInt(1, isSample);
	     }

         rset = pstmt.executeQuery();

         lineItemList = new ArrayList();
         LineItem lineItem = null;

         while(rset.next())
         {
            lineItem = new LineItem();
            lineItem.setLineItemId(rset.getInt("LineItem_ID"));
            lineItem.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
            lineItem.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
            lineItem.setCustomerId(rset.getInt("Customer_ID") );
            lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
            lineItem.setLineItemNo(rset.getInt("COOL_Line_Item_No"));
            lineItem.setQuantity(rset.getInt("COOL_Qty"));
            lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
            lineItem.setAmount(rset.getFloat("COOL_Amount"));
            lineItem.setRequestDate(StringUtils.formatDateToString(rset.getDate("COOL_Requested_Date"), null)+"");
            lineItem.setBookingDate(StringUtils.formatDateToString(rset.getDate("SO_Booking_Date"), null)+"");
            lineItem.setScheduleDate(StringUtils.formatDateToString(rset.getDate("COOL_Schedule_Date"), null));
            lineItem.setRevisedDate(StringUtils.formatDateToString(rset.getDate("COOL_Revised_Date"), null));
            lineItem.setStatus(rset.getString("COOL_Status"));
            lineItem.setProductId(rset.getInt("Product_ID"));
	        lineItem.setSalesOrderNo(rset.getString("SalesOrder_No"));
            lineItem.setOdmId(rset.getInt("SO_ODM_ID") );
	        lineItem.setCustomerPartNo(rset.getString("Customer_PartNumber"));
            SalesOrderDAO.loadHoldsForLineItem(lineItem); //load holds
            SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);//load partnumbers
	        String finalStatus = rset.getString("Final_COOL_Status");
            lineItem.setShippingTerms( rset.getString ( "SHIPPING_TERMS" ) );

            //System.out.println("Status --------"+finalStatus);

			if( !( ("SHIPPED".equalsIgnoreCase(finalStatus.trim())) || ("PARTIALLY SHIPPED".equalsIgnoreCase(finalStatus.trim()))
				 || ("ISSUED".equalsIgnoreCase(finalStatus.trim())) ) )
			{
				lineItemList.add(lineItem);
			}

         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return lineItemList;
   }

    //Added by Rohan on August 28th 2003 for searching the sales Hold/Release Orders
    // Added parameter isSample by Veeru on 16th Sep 2004
    public static ArrayList loadSalesOrdersInSalesHolds(String salesOrderNo,String marketingPartNo, String customerId, boolean isHold, String year, int isSample) throws ScalarException
   {
       String search_string = "";
        if((salesOrderNo!=null) && (salesOrderNo.trim().length()>0) )
        {
            search_string= " salesOrder_no like '" + StringUtils.formatToHandleQuote(salesOrderNo.trim()) + "%' ";
        }
        if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
        {
            if("".equals(search_string))
                  search_string = " Product_Id = " + MarketingDAO.loadProductIdByMfgPartNo(marketingPartNo);
                //search_string = " Product_Id = " + MarketingDAO.loadProductId(marketingPartNo);
            else
                search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdByMfgPartNo(marketingPartNo);
                //search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(marketingPartNo);
        }

       if((customerId != null) && (!"0".equals(customerId)))
       {
            if("".equals(search_string))
                search_string = " Customer_ID = " + Integer.parseInt(customerId);
           else
                search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);
       }

       int bookingYear = 0;
       if ( (year != null ) && ( year.trim ().length () != 0 ))
       {
           try
           {
               bookingYear= Integer.parseInt ( year);
           }
           catch ( NumberFormatException nfe )
           {
               bookingYear = 0;
           }
       }


        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList lineItemList = null;

        try {

         conn = DataSource.getConnection();
            if ( isHold )
            {
                if(bookingYear != 0)
                {
                    if("".equals(search_string))
                    {
                        search_string = " year(Sales_Order.SO_Booking_Date) = " + bookingYear;
                    }
                    else
                    {
                        search_string = search_string + " and year(Sales_Order.SO_Booking_Date) = " + bookingYear;
                    }

                }


                if ( "".equals ( search_string ) )
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.holds.loadHoldLineItemsWithooutPO" ) );
                else
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.holds.loadHoldLineItemsWithooutPO" ) + " and " + search_string );
            }
            else
            {    //original code is in comments below
                //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPO") + search_string);
                //Bhaskar added this statement below on 19 May 2003.
                // pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutApprovalAndShipped") + search_string);
                //System.out.println("ELSE");

                if(bookingYear != 0)
                {
                    if("".equals(search_string))
                    {
                        search_string = " year(SO_Booking_Date) = " + bookingYear;
                    }
                    else
                    {
                        search_string = search_string + " and year(SO_Booking_Date) = " + bookingYear;
                    }

                }


                if ( "".equals ( search_string ) )
                // pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutShippedAndPartiallyShipped"));
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.holds.loadLineItemsWithPOWithoutShippedAndPartiallyShipped" ) );
                else
                //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.holds.loadLineItemsWithooutPOWithoutShippedAndPartiallyShipped") + " where " + search_string);
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.holds.loadLineItemsWithPOWithoutShippedAndPartiallyShipped" ) + " where " + search_string );
                pstmt.setString ( 1, salesOrderNo + "%" );
                pstmt.setInt(2, isSample);
            }
            rset = pstmt.executeQuery ();

            lineItemList = new ArrayList ();
            LineItem lineItem = null;

         while(rset.next())
         {
            lineItem = new LineItem();
            lineItem.setLineItemId(rset.getInt("LineItem_ID"));
            lineItem.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
            lineItem.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
            lineItem.setCustomerId( rset.getInt("Customer_ID") );
            lineItem.setSalesOrderId(rset.getInt("SalesOrder_ID"));
            lineItem.setLineItemNo(rset.getInt("COOL_Line_Item_No"));
            lineItem.setQuantity(rset.getInt("COOL_Qty"));
            lineItem.setUnitPrice(rset.getFloat("COOL_Unit_Cost"));
            lineItem.setAmount(rset.getFloat("COOL_Amount"));
            lineItem.setRequestDate(StringUtils.formatDateToString(rset.getDate("COOL_Requested_Date"), null)+"");
            lineItem.setBookingDate(StringUtils.formatDateToString(rset.getDate("SO_Booking_Date"), null)+"");
            lineItem.setScheduleDate(StringUtils.formatDateToString(rset.getDate("COOL_Schedule_Date"), null));
            lineItem.setRevisedDate(StringUtils.formatDateToString(rset.getDate("COOL_Revised_Date"), null));
            lineItem.setStatus(rset.getString("COOL_Status"));
            lineItem.setProductId(rset.getInt("Product_ID"));
	        lineItem.setSalesOrderNo(rset.getString("SalesOrder_No"));
            lineItem.setOdmId(rset.getInt("SO_ODM_ID"));
            lineItem.setCustomerPartNo(rset.getString("Customer_PartNumber"));
            SalesOrderDAO.loadHoldsForLineItem(lineItem); //load holds
            SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);//load partnumbers
	        String finalStatus = rset.getString("Final_COOL_Status");
            lineItem.setShippingTerms( rset.getString ( "SHIPPING_TERMS" ) );
            //System.out.println("Status --------"+finalStatus);

	if( !( ("SHIPPED".equalsIgnoreCase(finalStatus.trim())) || ("PARTIALLY SHIPPED".equalsIgnoreCase(finalStatus.trim()))
		 || ("ISSUED".equalsIgnoreCase(finalStatus.trim())) ) )
	{
		lineItemList.add(lineItem);
	}


         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
            if (rset  != null) {
                try { rset.close(); } catch(SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return lineItemList;
   }
   //End of additions by Rohan on August 28th 2003

   public static void holdSalesOrderLineItems(String customerPO, boolean isHold,User user) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;
       try {

            conn = DataSource.getConnection();
            if(isHold)
				//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                //cstmt =  conn.prepareCall("{Call hold_all_lineitems(?,?,?,?,?,?)}");
				  cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.holdSalesOrderLineItems"));
            else
               // cstmt =  conn.prepareCall("{Call sp_release_all_lineitems(?,?,?,?,?,?)}");
                  cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.releaseSalesOrderLineItems"));

            cstmt.setString (1, customerPO);
            cstmt.setInt (2, -1); //As we are not holding by product
            cstmt.setInt (3, -1); //As we are not holding by customer
            cstmt.setString (4, user.getUserId());
            cstmt.setString (5, null); //For user name
            cstmt.setString (6, user.getGroupName());

            cstmt.execute();

            success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
          try {
                if (success == true) {
                    conn.commit();
                    //SalesDAO.createAlarmForHolds("This Customer PO =" + customerPO + "  has been hold by " + user.getUserId(), user);
                } else {
                    conn.rollback();
                }
           }
           catch (SQLException ignore) {}

          if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
          }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
          }
       }
   }


   public static void holdSalesOrderLineItems(String marketingPartNo, String customerId, boolean isHold,
                        User user) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;
       String productId = null;

       if((marketingPartNo == null) ||  ("".equals(marketingPartNo.trim())))
            productId = "0";
       else
            productId = Integer.toString(MarketingDAO.loadProductId(marketingPartNo));

       try {

            conn = DataSource.getConnection();
            if(isHold)
				//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                //cstmt =  conn.prepareCall("{Call hold_all_lineitems(?,?,?,?,?,?)}");
				  cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.holdSalesOrderLineItems"));
            else
                //cstmt =  conn.prepareCall("{Call sp_release_all_lineitems(?,?,?,?,?,?)}");
			       cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.releaseSalesOrderLineItems"));

            cstmt.setString (1, null); //As this hold is with product and customer
            cstmt.setInt (2, Integer.parseInt(productId));
            cstmt.setInt (3, Integer.parseInt(customerId));
            cstmt.setString (4, user.getUserId());
            cstmt.setString (5, null); //for user name
            cstmt.setString (6, user.getGroupName());

            cstmt.execute();

            success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
          try {
                if (success == true) {
                    conn.commit();
                } else {
                    conn.rollback();
                }
           }
           catch (SQLException ignore) {}

          if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
          }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
          }
       }
   }

   public static void holdSalesOrderLineItems(Iterator lineItemIds,User user,
                        boolean isHold) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;

       try {

            conn = DataSource.getConnection();

            while(lineItemIds.hasNext())
            {
                if(cstmt != null)
                {
                    cstmt.close();
                    cstmt = null;
                }

                if(isHold)
					//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                    //cstmt =  conn.prepareCall("{Call sp_hold_selected_lineItems(?,?,?,?)}");
					  cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.holdSelectedSalesOrderLineItems"));
                else
                    //cstmt =  conn.prepareCall("{Call sp_release_selected_lineItems(?,?,?,?)}");
				       cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.releaseSelectedSalesOrderLineItems"));

                cstmt.setInt(1,Integer.parseInt((String)lineItemIds.next()));
                cstmt.setString (2, user.getUserId());
                cstmt.setString (3, null); //user name
                cstmt.setString (4, user.getGroupName());;

                cstmt.execute();
            }

            success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
          try {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

          if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
          }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
          }
       }
   }

  public static void saveHoldsOnSalesOrderLineItems(Iterator list, User user, String comments) throws ScalarException
  {
            while(list.hasNext())
            {
                LineItem item = (LineItem) list.next();

                if((item.getMarketingHold() != null) && (item.getMarketingHold().trim().length() != 0))
                {
                    saveHoldsLineItems(item.getMarketingHold(), 1, "MARKETING", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }
                else if(item.getMarketingHold() != null)
                {
                    saveHoldsLineItems("", 0, "MARKETING", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }

                if((item.getOperationsHold() != null) && (item.getOperationsHold().trim().length() != 0))
                {
                    saveHoldsLineItems(item.getOperationsHold(), 1, "OPERATIONS", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }
                else if(item.getOperationsHold() != null)
                {
                    saveHoldsLineItems("", 0, "OPERATIONS", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }

                if((item.getSalesHold() != null) && (item.getSalesHold().trim().length() != 0))
                {
                    saveHoldsLineItems(item.getSalesHold(), 1, "SALES", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }
                else if(item.getSalesHold() != null)
                {
                    saveHoldsLineItems("", 0, "SALES", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }

                if((item.getFinanceHold() != null) && (item.getFinanceHold().trim().length() != 0))
                {
                    saveHoldsLineItems(item.getFinanceHold(), 1, "FINANCE", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }
                else if(item.getFinanceHold() != null)
                {
                    saveHoldsLineItems("", 0, "FINANCE", user.getUserId(), item.getLineItemId(),
                                item.getSalesOrderId(), item.getScheduleDate());
                }
            }
  }


   private static void saveHoldsLineItems(String reasonCode, int status, String group, String userId, int lineItemId,
                        int salesOrderId) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       boolean success = false;

       try {

            conn = DataSource.getConnection();
            //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
            //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");
              cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.holds.saveHoldsLineItems"));


            cstmt.setString (1, reasonCode);
            cstmt.setInt (2, status);
            cstmt.setString (3, group);
            cstmt.setString (4, userId);
            cstmt.setInt (5, lineItemId);
            cstmt.setInt (6, salesOrderId);
            cstmt.setString(7, null);

            cstmt.execute();

            success = true;

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
          try {
                if (success == true) {
                    conn.commit();
                } else {
                    conn.rollback();
                }
           }
           catch (SQLException ignore) {}

          if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
          }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
          }
       }
   }

    // Added by Prabhanjan for passing schedule date also while saving the holds. The procedure used by this method is updating schedule date. if null is passed the old schedule date is becoming null. From Sales/Holds Screen Schedule Date is not updated.
    // So, no need to pass null. Instead the old Schedule Date is passed.
    private static void saveHoldsLineItems ( String reasonCode, int status, String group, String userId, int lineItemId,
                                             int salesOrderId, String scheduleDate ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection ();
            //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
            //cstmt = conn.prepareCall ("{Call sp_approve_order_lineItem(?,?,?,?,?,?,?)}");
            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "sales.holds.saveHoldsLineItems" ) );

            cstmt.setString ( 1, reasonCode );
            cstmt.setInt    ( 2, status );
            cstmt.setString ( 3, group );
            cstmt.setString ( 4, userId );
            cstmt.setInt    ( 5, lineItemId );
            cstmt.setInt    ( 6, salesOrderId );
            cstmt.setDate   ( 7, StringUtils.getSQLDate ( scheduleDate, null ) );
            cstmt.execute   ();
            success = true;
        }
        catch ( Exception se )
        {
            // Exception while updating user profile
            // log it
            se.printStackTrace ();
        }
        finally
        {
            try
            {
                if ( success == true )
                {
                    conn.commit ();
                }
                else
                {
                    conn.rollback ();
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }


    }


    public static void saveHoldsLineItems ( DBTransaction dbTransaction, String reasonCode, int status, String group, String userId, int lineItemId, int salesOrderId, String scheduleDate ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "sales.holds.saveHoldsLineItems" ) );

            cstmt.setString ( 1, reasonCode );
            cstmt.setInt    ( 2, status );
            cstmt.setString ( 3, group );
            cstmt.setString ( 4, userId );
            cstmt.setInt    ( 5, lineItemId );
            cstmt.setInt    ( 6, salesOrderId );
            cstmt.setDate   ( 7, StringUtils.getSQLDate ( scheduleDate, null ) );
            cstmt.execute   ();
            success = true;
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            throw new ScalarException ( "Exception while saving the customer holds", se );
        }
        finally
        {
            try
            {
                if ( success == true )
                {
                    conn.commit ();
                }
                else
                {
                    conn.rollback ();
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }


    }



   private static void createAlarmForHolds(String comments, User user) throws ScalarException
   {
       try {
         // CREATE ORIGINATOR AND RECIPIENTS
         ArrayList recipients = new ArrayList();
         AlarmUser from = new AlarmUser (user.getUserId(), user.getEmail());

         //Loop thru all the users(approvers)
         //AlarmUser to = new AlarmUser ("User1", "achanala@hotmail.com");//get these from setup
         //recipients.add (to);
         //Create the to users
         AlarmUser to = null;

         // CREATE EXPIRE DATE
         Calendar cal = Calendar.getInstance();
         cal.set (Calendar.MONTH, 6);
         cal.set (Calendar.DAY_OF_MONTH, 15);
         Date expiresOn = cal.getTime();

         //This needs to be modified, if there is no need to send mail, if system automatically approves
         ArrayList approversList = new ArrayList();
         approversList.add(SalesDAO.loadFinanceApprovers(null));
         approversList.add(SalesDAO.loadMarketingApprovers(null));
         approversList.add(SalesDAO.loadSalesApprovers(null));
         approversList.add(SalesDAO.loadOperationsApprovers(null));

         for (int i=0; i<approversList.size(); i++)
         {
             ArrayList list = (ArrayList)approversList.get(i);
             for (int j=0; j<list.size(); j++)
             {
                User userBean = (User)list.get(j);
                to = new AlarmUser(userBean.getUserId(),userBean.getEmail());
                recipients.add(to);
             }
         }

          Alarm newAlarm = new Alarm ("Sales Order Notification", Constants.SALES_ORDER_ALARM, from);
          newAlarm.setPriority( Alarm.ALARM_PRIORITY_HIGH);
          newAlarm.setRecipients (recipients);

          // ACTIVATE
          newAlarm.activate(null);

          Comment comment = new Comment (to.getName(), comments);
          newAlarm.addComment (null, comment);

          //Notify all the reciepents
         newAlarm.notifyAllRecipients(false);

         newAlarm.close();

       } catch(Exception ex) {
            ex.printStackTrace();
                throw new ScalarException ("Could not create alarm", ex);
       }

   }

   public static ArrayList loadOnlineOrdersWithPurchaseOrder(String customerPO, int customerId, boolean isCustomer) throws ScalarException
   {
        if (customerPO == null) {
            return null;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            if(customerId == 0) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.onlineOrder.loadOnlineOrdersWithSalesOrderId"));
                pstmt.setString(1,customerPO);
            } else {
                pstmt = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.onlineOrder.loadOnlineOrdersForCustomers"));
                pstmt.setString(1,customerPO);
                pstmt.setInt(2,customerId);
            }

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("OnlineOrder_ID"));
                orders.setCustomerPO(rset.getString("OO_Customer_PO"));
                orders.setBookingDate(rset.getString("TimeStamp"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));

                pendingList.add(orders);
            }

            pstmt.close(); pstmt = null;
            rset.close(); rset = null;

            if(isCustomer) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.onlineOrder.loadSalesOrdersForCustomersPO"));
                pstmt.setString(1,customerPO);
                pstmt.setInt(2,customerId);

                rset = pstmt.executeQuery();

                while (rset.next()) {
                    orders = new Orders();
                    orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                    orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                    orders.setBookingDate(rset.getString("SO_Booking_Date"));
                    orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                    orders.setStatus(loadStatus(null,orders.getSalesOrderId()));

                    pendingList.add(orders);
                }
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load online orders for purchaseOrder from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadAllOnlineOrders(int customerId, String marketingPartNo, boolean isCustomer)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList pendingList = new ArrayList();
        ResultSet rset = null;

        try {

            conn = DataSource.getConnection();
            String searchString = "";
            int productId = 0;

            if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
                 productId =  MarketingDAO.loadProductId(marketingPartNo);
            if(customerId != 0)
                searchString = searchString + " and Customer_Id = " + customerId;
            if((marketingPartNo != null) && (marketingPartNo.trim().length() > 0))
                searchString = searchString + " and Product_Id = " + productId;

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.onlineOrder.loadAllOnlineOrders") + searchString);

            rset = pstmt.executeQuery();
            Orders orders = null;

            while (rset.next()) {
                orders = new Orders();
                orders.setSalesOrderId(rset.getInt("OnlineOrder_ID"));
                orders.setCustomerPO(rset.getString("OO_Customer_PO"));
                orders.setBookingDate(rset.getString("TimeStamp"));
                orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                orders.setStatus("Pending");
                pendingList.add(orders);
            }


        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load online orders from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return pendingList;
   }

   public static ArrayList loadRegionSalesApprovers(DBTransaction dbTransaction, int salesOrderId)
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList approvers = new ArrayList();
        ResultSet rset = null;
        User user = null;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                    conn = DataSource.getConnection();
             }
             else {
                    conn = dbTransaction.getConnection();
            }

            user = new User();
            user.setUserId("johny");
            user.setEmail("achanala@hotmail.com");
            approvers.add(user);
            //Complete the code here...
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load online orders from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return approvers;
   }

// modified by prabhanjan on 28th October 2003 for providing additional search on sales order number.
// public static ArrayList loadOrdersForScheduling(String customerId, String productId)
// public static ArrayList loadOrdersForScheduling ( String customerId, String productId, String salesOrderNo, String bookingYear )
   // Signature Modified. Booking Year added as search criteria. Prabhanjan (06 Apr 2004)
   public static ArrayList loadOrdersForScheduling ( String customerId, String productId, String salesOrderNo, String bookingYear )
                                throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList lineItemList = new ArrayList();
        ResultSet rs = null;

        try {

            conn = DataSource.getConnection();
            String searchString = "";

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.scheduling.loadAllOrdersToSchedule"));
            pstmt.setString ( 1, productId );
            pstmt.setString ( 2, customerId );
            pstmt.setString ( 3, salesOrderNo );  // added by prabhanjan
            pstmt.setString ( 4, bookingYear );   // added by Prabhanjan (06 Apr 2004)

            rs = pstmt.executeQuery();
            LineItem lineItem = null;

            while (rs.next()) {
                lineItem = new LineItem();
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
                lineItem.setUnitPrice(rs.getFloat("COOL_Unit_Cost"));
                lineItem.setAmount(rs.getFloat("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("Schedule_Date"),null));
                lineItem.setSalesOrderId(rs.getInt("SalesOrder_ID"));
                lineItem.setCustomerName(CustomerDAO.loadCustomerName(rs.getInt("Customer_ID")));
                lineItem.setCustomerId(rs.getInt("Customer_ID"));
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));
                // Added by Prabhanjan (06 Apr 2004)
                lineItem.setCustomerPartNo ( rs.getString ( "Customer_PartNumber" ) );
                lineItem.setBookingDate( StringUtils.formatDateToString(rs.getDate("SO_Booking_Date") ) );
                lineItem.setOdmId(rs.getInt("SO_ODM_ID"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                //if ((suggDate == null) || (suggDate.trim().length() == 0)) { // Added by Krishna 27 July 2004
                //    suggDate = lineItem.getScheduleDate();
                //}
                if ( ( suggDate != null ) && ( suggDate.trim().length() > 0 ) ) {
                    lineItem.setSuggScheduleDate (suggDate);
                }

                String scheduleDate = lineItem.getScheduleDate();
                if ((scheduleDate == null) || (scheduleDate.trim().length() == 0)) {
                    if ( ( suggDate != null ) && ( suggDate.trim().length() > 0 ) ) {
                        lineItem.setScheduleDate (suggDate);
                    }
                }

                lineItem = SalesOrderDAO.loadMarketingAndMfgPartNumbers(lineItem);
                //lineItem.setSuggScheduleDate(loadSuggScheduleDate(lineItem.getMarketingPartNo(),lineItem.getQuantity()));

                lineItemList.add(lineItem);
            }


        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load LineItems from database", sqle);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return lineItemList;
   }

    public static String loadSuggScheduleDate(String marketingPartNo, int quantity) throws ScalarException
   {
       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;
       boolean success = false;
       java.util.Date suggDate = null;

       try {

            conn = DataSource.getConnection();

            //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
            //cstmt =  conn.prepareCall("{Call sp_order_scheduling(?,?,?)}");
			cstmt =  conn.prepareCall(SqlQueryMappings.get ("sales.salesOrder.loadSuggScheduleDate"));
            cstmt.registerOutParameter(1, Types.TIMESTAMP);
            cstmt.setString(2, marketingPartNo);
            cstmt.setInt(3, quantity);

            cstmt.execute();
            suggDate = cstmt.getDate(1);
            success = true;
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       }
       finally {
          if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
          }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
          }
          if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
          }
       }
       //return suggDate;
       return StringUtils.formatDateToString(suggDate,"");
   }


   /*
    * Schedule SalesOrders
    *
    */
    public static boolean scheduleOrders(DBTransaction dbTransaction, String[] lineItemIds, String[] scheduleDates, User user) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt =  conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.scheduling.scheduleOrders"));

            for(int i=0; i<lineItemIds.length; i++) {

                if ((scheduleDates[i] != null) && (scheduleDates[i].trim().length() != 0)) {

                    cstmt.setInt(1, Integer.parseInt(lineItemIds[i]));
                    cstmt.setDate(2, StringUtils.getSQLDate(scheduleDates[i],null));
                    cstmt.setString(3, user.getUserId());
                    cstmt.setString(4, user.getGroupName());

                    cstmt.addBatch();
                }
            }

            cstmt.executeBatch();

            success = true;

        }

        catch (Exception se) {
            // Exception while updating user profile
            // log it
            se.printStackTrace();
            throw new ScalarException ("Exception while scheduling the orders", se);
        }

        finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       return true;
    }


    // Added By Balaram on Dec 24, 2002
    public static void scheduleOrdersByLineItemNumber (DBTransaction dbTransaction, String lineItemNumber, String salesOrderId, String scheduleDate, User user) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        if ((scheduleDate == null) || (scheduleDate.trim().length() == 0) || (user == null)) {
            return;
        }

        try {

            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt =  conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.scheduling.scheduleOrdersByLineItemNumber"));

            cstmt.setInt (1, Integer.parseInt (lineItemNumber));
            cstmt.setInt (2, Integer.parseInt (salesOrderId));
            cstmt.setDate (3, StringUtils.getSQLDate (scheduleDate,null));
            cstmt.setString (4, user.getUserId());
            cstmt.setString (5, user.getGroupName());

            cstmt.execute();
            success = true;

        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while scheduling the order for lineItem# " + lineItemNumber + ", salesOrder ID: " + salesOrderId, se);
        }

        finally {

            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) { conn.commit(); }
                        else {conn.rollback(); }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }




   /*
    * Get Inventory
    *
    */
    public static ArrayList loadInventory(String marketingPartNo) throws ScalarException
    {

       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;
       ArrayList list = new ArrayList();
       try {

         conn = DataSource.getConnection();

         if ((marketingPartNo != null) && (marketingPartNo.trim().length() != 0)) {
			  //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
               // cstmt =  conn.prepareCall("{Call sp_current_inventory(?)}");
   		          cstmt =  conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.loadInventory"));

                cstmt.setString(1,marketingPartNo);

                rs = cstmt.executeQuery();
                list = new ArrayList();
                Inventory inventory = null;
                while(rs.next())
                {
                    inventory = new Inventory();
                    inventory.setProcessType(rs.getString("Process"));
                    inventory.setGoodUnits(rs.getInt("Quantity"));
                    inventory.setHoldUnits(rs.getInt("Hold_Quantity"));

                    list.add(inventory);
                }
            }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }
       return list;
    }

    /*
    * Get Shippable Units
    *
    */
    private static Inventory loadShippableUnits(String marketingPartNo) throws ScalarException
    {

       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;
       Inventory shippableUnits = null;

       try {

         conn = DataSource.getConnection();

         if ((marketingPartNo != null) && (marketingPartNo.trim().length() != 0)) {
			    //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                //cstmt =  conn.prepareCall("{Call sp_shippable_units_and_bl_sch_date(?)}");
				  cstmt =  conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.loadShippableUnits"));

                cstmt.setString(1,marketingPartNo);

                rs = cstmt.executeQuery();
                shippableUnits = getSchedulingUnits(rs);

            }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }

       return shippableUnits;
    }

    /*
    * Get Shippable Units
    *
    */
    private static ArrayList loadScheduleAndRequestedDateUnits(String marketingPartNo) throws ScalarException
    {

       Connection conn = null;
       CallableStatement cstmt = null;
       ResultSet rs = null;
       ArrayList list = null;

       try {

         conn = DataSource.getConnection();

         if ((marketingPartNo != null) && (marketingPartNo.trim().length() != 0)) {
                list = new ArrayList();
				//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
                //cstmt =  conn.prepareCall("{Call sp_order_schedhuling_dates(?)}");
				  cstmt =  conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.loadScheduleAndRequestedDateUnits"));

                cstmt.setString(1,marketingPartNo);

                rs = cstmt.executeQuery();

                list.add(getSchedulingUnits(rs));

                //Loop through all the Request Date Units
                cstmt.getMoreResults();
                rs = cstmt.getResultSet();
                list.add(getSchedulingUnits(rs));

            }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }

       return list;
    }



    // Added By Balaram
    public static HashMap loadDeltaScheduleUnits (String mktPartNumber) throws ScalarException
    {
        HashMap entireLotQty = getLotQuantityByWeek (mktPartNumber, ENTIRE_LOT_QUANTITY);
        HashMap reservedLotQty = getLotQuantityByWeek (mktPartNumber, RESERVED_LOT_QUANTITY);

        Iterator steps = null;
        HashMap psLotQtyMap1 = null;
        HashMap psLotQtyMap2 = null;
        LotQuantity psLotQty1 = null;
        LotQuantity psLotQty2 = null;
        String psName = null;
        int qty1 = 0;
        int qty2 = 0;

        //kumar from here
        Iterator lotList1 = null;
        Iterator lotList2 = null;
        LotNumberQtyAndDateOut lotDetails1 = null;
        LotNumberQtyAndDateOut lotDetails2 = null;
        //till here

        for (int i=1; i<=13; i++) {
            psLotQtyMap1 = (HashMap) entireLotQty.get (i+"");
            psLotQtyMap2 = (HashMap) reservedLotQty.get (i+"");

            if ((psLotQtyMap1 == null) || (psLotQtyMap2 == null)) {
                continue;
            }

            steps = psLotQtyMap1.keySet().iterator();

            while (steps.hasNext()) {
                psName = (String) steps.next();

                psLotQty1 = (LotQuantity) psLotQtyMap1.get (psName);
                psLotQty2 = (LotQuantity) psLotQtyMap2.get (psName);

                if ((psLotQty1 != null) && (psLotQty2 != null)) {
                    psLotQty1.setQuantity(psLotQty1.getQuantity() - psLotQty2.getQuantity());
                }

                if (psLotQty2 == null) {
                    continue;
                }

                //getting all the lots in this LotQuantity Object -- kumar
                lotList1 = psLotQty1.getLotNumberQtyList().iterator();
                lotList2 = psLotQty2.getLotNumberQtyList().iterator();

                //Prepare HashMap for reserved lotQytList
                HashMap lotList2Map = new HashMap();

                while(lotList2.hasNext())
                {
                    lotDetails2 = (LotNumberQtyAndDateOut) lotList2.next();
                    int lotDetails2Qty = lotDetails2.getQuantity();
                    String previousQty = (String)lotList2Map.get(lotDetails2.getLotNumber());
                    if(previousQty != null) {
                         lotDetails2Qty += Integer.parseInt(previousQty);
                    }
                    lotList2Map.put(lotDetails2.getLotNumber(),lotDetails2Qty+"");
                }

                //Loop thru the entire lotQtyList
                while(lotList1.hasNext())
                {
                    lotDetails1 = (LotNumberQtyAndDateOut) lotList1.next();
                    String lotNumber = lotDetails1.getLotNumber();
                    int lotQty = lotDetails1.getQuantity();

                    String reservLotQty = (String)lotList2Map.get(lotNumber);
                    int remainingLotQty = 0;

                    if(reservLotQty != null) { //this lotnumber exists in the reserved lots
                        remainingLotQty = lotQty - Integer.parseInt(reservLotQty);
                        if(remainingLotQty == 0) {
                            lotList1.remove(); //remove the lot when the remaining qty is zero
                        } else {
                            lotDetails1.setQuantity(remainingLotQty);  //update with remaining qty
                        }
                    }

                }
                //till here

            }
        }


        return entireLotQty;
    }

    //For showing the shippable units by day for lots
    public static HashMap loadAllLotDetailsByWeek(String mktPartNumber) throws ScalarException
    {
        return  getLotQuantityByWeek (mktPartNumber, ENTIRE_LOT_QUANTITY);
    }

    public static HashMap loadAllBackLogByWeek(String mktPartNumber) throws ScalarException
    {
        return getBackLogByWeek(mktPartNumber,BY_SCHEDULE_DATE);
    }

    // Completely re-coded - Balaram on Dec 07, 2002
    public static ArrayList loadAllScheduleUnits(String marketingPartNo) throws ScalarException
    {
        ArrayList list = new ArrayList();

        // Step 1
        // Get entire lot quantity
        Inventory entireLotInventory = getLotInventory (marketingPartNo, ENTIRE_LOT_QUANTITY, "Shippable Units");
        list.add (entireLotInventory);

        // Step 2
        // Get reserved lot quantity
        Inventory reservedLotInventory = getLotInventory (marketingPartNo, RESERVED_LOT_QUANTITY, "Reserved Units");
        list.add (reservedLotInventory);

        // Step 3
        // Calculate Delta
        Inventory deltaInventory = entireLotInventory.getDelta (reservedLotInventory);
        if(deltaInventory==null)
            throw new ScalarException("Error while loading schedule units");
        deltaInventory.setProcessType ("Delta Availability");
        list.add (deltaInventory);

        // Step 4
        // Calculate Cumulative
        Inventory cumulativeInventory = deltaInventory.getCumulative();
        if(cumulativeInventory==null)
            throw new ScalarException("Error while loading schedule units");
        cumulativeInventory.setProcessType ("Cumulative Availability");
        list.add (cumulativeInventory);

        // Step 5
        // Get backlog by schedule date
        Inventory backlogInventoryByScheduledDate = getBackLog (marketingPartNo, BY_SCHEDULE_DATE, "B/L By sch. Date");
        list.add (backlogInventoryByScheduledDate);


        // Step 6
        // Get backlog by requested date
        Inventory backlogInventoryByrequestedDate = getBackLog (marketingPartNo, BY_REQUESTED_DATE, "B/L By req. Date");
        list.add (backlogInventoryByrequestedDate);

        return list;


        /**************************************  COMMENTED OUT BY BALARAM ON DEC 07, 2002 *****************\

         Inventory shippableUnits = loadShippableUnits(marketingPartNo);
        shippableUnits.setProcessType("Shippable Units");
        ArrayList list1 = loadScheduleAndRequestedDateUnits(marketingPartNo);

        Inventory scheduleDateUnits = (Inventory)list1.get(0);
        scheduleDateUnits.setProcessType("B/L By sch. Date");


        //calculate and set the delta avialability (it is shippableUnits - scheduledUnits)
        Inventory deltaUnits = new Inventory((shippableUnits.getWeek1() - scheduleDateUnits.getWeek1()),
                                             (shippableUnits.getWeek2() - scheduleDateUnits.getWeek2()),
                                             (shippableUnits.getWeek3() - scheduleDateUnits.getWeek3()),
                                             (shippableUnits.getWeek4() - scheduleDateUnits.getWeek4()),
                                             (shippableUnits.getWeek5() - scheduleDateUnits.getWeek5()),
                                             (shippableUnits.getWeek6() - scheduleDateUnits.getWeek6()),
                                             (shippableUnits.getWeek7() - scheduleDateUnits.getWeek7()),
                                             (shippableUnits.getWeek8() - scheduleDateUnits.getWeek8()),
                                             (shippableUnits.getWeek9() - scheduleDateUnits.getWeek9()),
                                             (shippableUnits.getWeek10() - scheduleDateUnits.getWeek10()),
                                             (shippableUnits.getWeek11() - scheduleDateUnits.getWeek11()),
                                             (shippableUnits.getWeek12() - scheduleDateUnits.getWeek12()),
                                             (shippableUnits.getWeek13() - scheduleDateUnits.getWeek13()));
        deltaUnits.setProcessType("Delta Availability");

        //calculate and set the cumulative avialability (adding to previous month)
        Inventory cumulativeUnits = new Inventory();
        cumulativeUnits.setWeek1(deltaUnits.getWeek1());
        cumulativeUnits.setWeek2(deltaUnits.getWeek1() + deltaUnits.getWeek2());
        cumulativeUnits.setWeek3(cumulativeUnits.getWeek2() + deltaUnits.getWeek3());
        cumulativeUnits.setWeek4(cumulativeUnits.getWeek3() + deltaUnits.getWeek4());
        cumulativeUnits.setWeek5(cumulativeUnits.getWeek4() + deltaUnits.getWeek5());
        cumulativeUnits.setWeek6(cumulativeUnits.getWeek5() + deltaUnits.getWeek6());
        cumulativeUnits.setWeek7(cumulativeUnits.getWeek6() + deltaUnits.getWeek7());
        cumulativeUnits.setWeek8(cumulativeUnits.getWeek7() + deltaUnits.getWeek8());
        cumulativeUnits.setWeek9(cumulativeUnits.getWeek8() + deltaUnits.getWeek9());
        cumulativeUnits.setWeek10(cumulativeUnits.getWeek9() + deltaUnits.getWeek10());
        cumulativeUnits.setWeek11(cumulativeUnits.getWeek10() + deltaUnits.getWeek11());
        cumulativeUnits.setWeek12(cumulativeUnits.getWeek11() + deltaUnits.getWeek12());
        cumulativeUnits.setWeek13(cumulativeUnits.getWeek12() + deltaUnits.getWeek13());
        cumulativeUnits.setProcessType("Cumulative Availability");

         Inventory requestDateUnits = (Inventory) list1.get(1);
        requestDateUnits.setProcessType("B/L By req. Date");

        ArrayList list = new ArrayList();
        list.add(shippableUnits);
        list.add(scheduleDateUnits);
        list.add(deltaUnits);
        list.add(cumulativeUnits);
        list.add(requestDateUnits);

        return list;

        \********************************************************************************************************/
    }

    // Added by Balaram
    private static Inventory getLotInventory (String mktPartNumber, int qtyCriteria, String processType) throws ScalarException {

        Inventory entireLotInventory = new Inventory();
        entireLotInventory.setProcessType (processType);

        HashMap lotQtyMap = getLotQuantityByWeek (mktPartNumber, qtyCriteria);
        Iterator keys = lotQtyMap.keySet().iterator();
        Iterator steps = null;
        HashMap stepQtyMap = null;
        String week = null;
        String psName = null;
        LotQuantity psLotQuantity = null;
        int qty = 0;
        int totalQty = 0;

        while (keys.hasNext()) {

            week = (String) keys.next();
            stepQtyMap = (HashMap) lotQtyMap.get (week);
            steps = stepQtyMap.keySet().iterator();

            while (steps.hasNext()) {
                psName = (String) steps.next();
                psLotQuantity = (LotQuantity) stepQtyMap.get (psName);

                qty = psLotQuantity.getQuantity();
                totalQty = totalQty + qty;

            }

            entireLotInventory.setQtyByWeek (week, totalQty);
            totalQty = 0;
        }

        return entireLotInventory;
    }


    // Added by Balaram
    private static Inventory getBackLog (String mktPartNumber, int dateCriteria, String processType) throws ScalarException {

        Inventory backLogInventory = new Inventory();
        backLogInventory.setProcessType (processType);

        HashMap weekQtyMap = getBackLogByWeek (mktPartNumber, dateCriteria);
        Iterator weeks = weekQtyMap.keySet().iterator();
        String weekNumber = null;

        while (weeks.hasNext()) {
            weekNumber = (String) weeks.next();
            ReservedQuantity reservedQty = (ReservedQuantity) weekQtyMap.get (weekNumber);
            backLogInventory.setQtyByWeek (weekNumber,reservedQty.getQuantity());
        }

        return backLogInventory;
    }




    public static Inventory getSchedulingUnits(ResultSet rs) throws ScalarException
    {
        Inventory units = new Inventory();

        try {
                while(rs.next())
                {
                    units.setWeek1(rs.getInt("week1"));
                    units.setWeek2(rs.getInt("week2"));
                    units.setWeek3(rs.getInt("week3"));
                    units.setWeek4(rs.getInt("week4"));
                    units.setWeek5(rs.getInt("week5"));
                    units.setWeek6(rs.getInt("week6"));
                    units.setWeek7(rs.getInt("week7"));
                    units.setWeek8(rs.getInt("week8"));
                    units.setWeek9(rs.getInt("week9"));
                    units.setWeek10(rs.getInt("week10"));
                    units.setWeek11(rs.getInt("week11"));
                    units.setWeek12(rs.getInt("week12"));
                    units.setWeek13(rs.getInt("week13"));

                }


        } catch(Exception ex) { System.out.println(ex);}

        return units;
    }



    // Added by Balaram - on Nov 30, 2002
    public static int getTransitTimeForStep (String stepName, String marketingPartNo, int timeForManualProcess) throws ScalarException {

        //int transitTime = 67; commented after checking it is working properly -- kumar
        int transitTime = 0;
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            cstmt =  conn.prepareCall (SqlQueryMappings.get ("operations.process.getTransitTimeForStep"));
            cstmt.registerOutParameter (1, Types.INTEGER);
            cstmt.setInt (2, timeForManualProcess);
            cstmt.setString (3, marketingPartNo);
            cstmt.setString (4, stepName);

            cstmt.execute();
            transitTime = cstmt.getInt(1);
            success = true;
        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while trying to get Transit time for step: " + stepName + ", Mkt Partnumber: " + marketingPartNo, se);
        }

        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

        return transitTime;
    }

    /**
     *  Week_Number ---
     *               |
     *              ---- (Process Step name -> quantity, {(Lot_Number - lot_Qty), (Lot_Number - lot_Qty), ...}
     *               |
     *              ---- (Process Step name -> quantity, {(Lot_Number - lot_Qty), (Lot_Number - lot_Qty), ...}
     *
     *  Week_Number ---
     *               |
     *              ---- (Process Step name -> quantity, {(Lot_Number - lot_Qty), (Lot_Number - lot_Qty), ...}
     *               |
     *              ---- (Process Step name -> quantity, {(Lot_Number - lot_Qty), (Lot_Number - lot_Qty), ...}
     *
     */

    // Added by Balaram - on Dec 07, 2002
    public static HashMap getLotQuantityByWeek (String mktPartNumber, int qtyCriteria) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap lotQuantityByWeek = new HashMap();
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            if (qtyCriteria == RESERVED_LOT_QUANTITY) {
                //pstmt =  conn.prepareStatement (SqlQueryMappings.get ("operations.process.getReservedLotQuantityByWeek"));

				//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
			     // pstmt = conn.prepareStatement ("SELECT * FROM fn_get_lot_reserved_quantity(?)");
			      pstmt =  conn.prepareStatement (SqlQueryMappings.get ("operations.process.getReservedLotQuantity"));
                pstmt.setString (1, mktPartNumber);
            }
            else if (qtyCriteria == ENTIRE_LOT_QUANTITY) {
                pstmt =  conn.prepareStatement (SqlQueryMappings.get ("operations.process.getLotQuantityByWeek"));
                pstmt.setString (1, mktPartNumber);
                pstmt.setDate (2, new java.sql.Date (System.currentTimeMillis()));
            }
            else {
                throw new ScalarException ("SalesDAO.getLotQuantityByWeek() - Invalid Quantity Criteria");
            }

            String processStepName = null;
            int lotQuantity = 0;
            java.sql.Date expectedDateOut = null;
            String lotNumber = null;
            String lotStatus = null;

            //Date now = new Date();
            long daysRemainingToExpectedDateOut = 0;
            long weekNumber = 0;
            int stepLotQuantity = 0;
            HashMap stepQtyMap = null;
            LotQuantity processStepLotQuantity = null;

            rset = pstmt.executeQuery();
            while (rset.next()) {

                processStepName = rset.getString ("ProcessStep_Name");
                lotQuantity = rset.getInt ("Lot_Quantity");
                expectedDateOut = rset.getDate ("Expected_Date_Out");
                lotNumber = rset.getString ("Lot_Number");
                lotStatus = rset.getString ("Lot_Status");

                if (expectedDateOut == null) {
                    continue;
                }

              	Calendar nowCal = new GregorianCalendar();
               	nowCal.setTime(new Date());
        	Date now = new GregorianCalendar(nowCal.get(Calendar.YEAR), nowCal.get(Calendar.MONTH), nowCal.get(Calendar.DATE)).getTime();

                daysRemainingToExpectedDateOut = ((expectedDateOut.getTime() - now.getTime())/(1000*60*60*24));
                //double days = ((expectedDateOut.getTime() - now.getTime())/(1000*60*60*24));
                if (daysRemainingToExpectedDateOut < 0) {
                    weekNumber = 1;
                }
                else {
                    //weekNumber = Math.round(days/7) + 1;
                    weekNumber = (daysRemainingToExpectedDateOut/7) + 1;

                }

                stepQtyMap = (HashMap) lotQuantityByWeek.get (weekNumber+"");
                if (stepQtyMap == null) {
                    stepQtyMap = new HashMap();
                    lotQuantityByWeek.put (weekNumber+"", stepQtyMap);
                }

                processStepLotQuantity = (LotQuantity) stepQtyMap.get (processStepName);
                if (processStepLotQuantity == null) {
                    processStepLotQuantity = new LotQuantity();
                    processStepLotQuantity.setProcessStepName (processStepName);

                    stepQtyMap.put (processStepName, processStepLotQuantity);
                }

                stepLotQuantity = processStepLotQuantity.getQuantity();
                processStepLotQuantity.setQuantity (lotQuantity+stepLotQuantity);
                processStepLotQuantity.setLotNumberQuantityAndExpectedDateOut (lotNumber, lotQuantity, expectedDateOut, lotStatus);
            }


            /*
            Iterator keys = lotQuantityByWeek.keySet().iterator();
            Iterator steps = null;
            HashMap temp = null;
            String week = null;
            LotQuantity psLotQty = null;
            String psName = null;

            while (keys.hasNext()) {
                week = (String) keys.next();

                temp = (HashMap) lotQuantityByWeek.get (week);

                steps = temp.keySet().iterator();
                while (steps.hasNext()) {
                    psName = (String) steps.next();
                    psLotQty = (LotQuantity) temp.get (psName);

                }
            }
            */


            success = true;
        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while trying to get lot quantity for Mkt Partnumber: " + mktPartNumber, se);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

        return lotQuantityByWeek;
    }


    public static HashMap getReservedLotQuantityByLineItem (String lineItemNumberStr, String salesOrderIdStr) throws ScalarException {

        int lineItemNumber = -1;
        int salesOrderId = -1;

        try {
            lineItemNumber = Integer.parseInt (lineItemNumberStr);
            salesOrderId = Integer.parseInt (salesOrderIdStr);
        }
        catch (NumberFormatException nfe) {
            // invalid values
        }

        return getReservedLotQuantityByLineItem (lineItemNumber, salesOrderId);
    }

    public static HashMap getReservedLotQuantityByLineItem (int lineItemNumber, int salesOrderId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap lotQuantityByWeek = new HashMap();
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            //pstmt =  conn.prepareStatement (SqlQueryMappings.get ("operations.process.getReservedLotQuantityByLineItem"));

			//Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

           // pstmt =  conn.prepareStatement ("SELECT * FROM fn_get_lineItem_reserved_lots(?,?)"); //replaced above query with function
			pstmt =  conn.prepareStatement (SqlQueryMappings.get ("operations.process.getReservedLotsByLineItem"));
            pstmt.setInt (1, lineItemNumber);
            pstmt.setInt (2, salesOrderId);

            rset = pstmt.executeQuery();

            String processStepName = null;
            int lotQuantity = 0;
            java.sql.Date expectedDateOut = null;
            String lotNumber = null;

            Date now = new Date();
            long daysRemainingToExpectedDateOut = 0;
            long weekNumber = 0;
            int stepLotQuantity = 0;
            HashMap stepQtyMap = null;
            LotQuantity processStepLotQuantity = null;

            while (rset.next()) {

                processStepName = rset.getString ("ProcessStep_Name");
                lotQuantity = rset.getInt ("Lot_Quantity");
                expectedDateOut = rset.getDate ("Expected_Date_Out");
                lotNumber = rset.getString ("Lot_Number");

                if (expectedDateOut == null) {
                    continue;
                }

                daysRemainingToExpectedDateOut = ((expectedDateOut.getTime() - now.getTime())/(1000*60*60*24));
                if (daysRemainingToExpectedDateOut < 0) {
                    weekNumber = 1;
                }
                else {
                    weekNumber = (daysRemainingToExpectedDateOut/7) + 1;
                }

                stepQtyMap = (HashMap) lotQuantityByWeek.get (weekNumber+"");
                if (stepQtyMap == null) {
                    stepQtyMap = new HashMap();
                    lotQuantityByWeek.put (weekNumber+"", stepQtyMap);
                }

                processStepLotQuantity = (LotQuantity) stepQtyMap.get (processStepName);
                if (processStepLotQuantity == null) {
                    processStepLotQuantity = new LotQuantity();
                    processStepLotQuantity.setProcessStepName (processStepName);

                    stepQtyMap.put (processStepName, processStepLotQuantity);
                }

                stepLotQuantity = processStepLotQuantity.getQuantity();
                processStepLotQuantity.setQuantity (lotQuantity+stepLotQuantity);
                processStepLotQuantity.setLotNumberQuantityAndExpectedDateOut (lotNumber, lotQuantity, expectedDateOut);
            }


            /*
            Iterator keys = lotQuantityByWeek.keySet().iterator();
            Iterator steps = null;
            HashMap temp = null;
            String week = null;
            LotQuantity psLotQty = null;
            String psName = null;

            while (keys.hasNext()) {
                week = (String) keys.next();

                temp = (HashMap) lotQuantityByWeek.get (week);

                steps = temp.keySet().iterator();
                while (steps.hasNext()) {
                    psName = (String) steps.next();
                    psLotQty = (LotQuantity) temp.get (psName);
                }
            }
            */


            success = true;
        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while trying to get reserved lot quantity for line Number: " + lineItemNumber + " and salesOrder ID: " + salesOrderId , se);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

        return lotQuantityByWeek;
    }



    public static HashMap getBackLogByWeek (String mktPartNumber, int dateCriteria) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        HashMap backLogByWeekMap = new HashMap();
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            if (dateCriteria == BY_SCHEDULE_DATE) {
                cstmt = conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.getBackLogByWeekByScheduleDate"));
            }
            else if (dateCriteria == BY_REQUESTED_DATE) {
                cstmt = conn.prepareCall (SqlQueryMappings.get ("sales.salesOrder.getBackLogByWeekByRequestDate"));
            }
            else {
                throw new ScalarException ("SalesDAO.getBackLogByWeek() - Invalid Date Criteria");
            }

            cstmt.setString (1, mktPartNumber);
            rs = cstmt.executeQuery();

            Date now = new Date();
            java.sql.Date resultDate = null;
            int quantity = 0;
            int totalQty = 0;
            long days = 0;
            long weekNumber = 0;
            ReservedQuantity reservedQty = null;
            int lineItemNo = 0;
            String salesOrderNo = null;
            String customerName = null;

            while (rs.next()) {
                resultDate = rs.getDate ("COOL_Date");
                if (resultDate == null) {
                    continue;
                }

                quantity = rs.getInt ("COOL_Qty");
                lineItemNo = rs.getInt("COOL_Line_Item_No");
                salesOrderNo = rs.getString("SalesOrder_No");
                customerName = rs.getString("C_Name");

                days = ((resultDate.getTime() - now.getTime())/(1000*60*60*24));
                if (days < 0) {
                    continue;
                }

                weekNumber = (days/7) + 1;
                /*
                try {
                    totalQty = Integer.parseInt ((String)backLogByWeekMap.get (weekNumber+""));
                }
                catch (NumberFormatException nfe) {
                    // got null
                }

                totalQty = totalQty + quantity;
                backLogByWeekMap.put (weekNumber+"", totalQty+"");
                 *
                */
                //Added by Kumar On 25 Apr 2003 for showing the units by salesorder in popup, in order scheduling
                reservedQty = (ReservedQuantity) backLogByWeekMap.get (weekNumber+"");
                if (reservedQty == null) {
                    reservedQty = new ReservedQuantity();
                    backLogByWeekMap.put (weekNumber+"", reservedQty);
                }

                totalQty = reservedQty.getQuantity();
                reservedQty.setQuantity (quantity+totalQty);
                reservedQty.setLineItemQuantityAndschDates (salesOrderNo, lineItemNo, quantity, StringUtils.formatDateToString(resultDate), customerName);

                totalQty = 0;
            }
            success = true;
        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while trying to get backlog information for " + mktPartNumber, se);
        }

        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

        return backLogByWeekMap;
    }

    public static ArrayList loadSalesTaxList() throws ScalarException {
        HashMap map = loadSalesTaxes();
        ArrayList list = new ArrayList();

        //System.out.println("If there are no records:"+map);

        Iterator states = map.keySet().iterator();
        Iterator taxes = map.values().iterator();

        //System.out.println("States:"+states);

        //Loop thru the tax list and show the values
        while(states.hasNext())
        {
            //System.out.println("States While Loop");
            String state = (String)states.next();
            String tax = (String)taxes.next();

            float taxFloat = Float.parseFloat(tax)/100;
            list.add(new org.apache.struts.util.LabelValueBean(state+" - "+tax,state ));
        }

        return list;
    }

    public static HashMap loadSalesTaxes() throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       HashMap taxes = new HashMap();

       try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.taxAndTerms.loadSalesTaxes"));
         rs = pstmt.executeQuery();

         while(rs.next())
         {
             taxes.put(rs.getString("state"), Float.toString(rs.getFloat("tax")));
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

       /* Commented by Basha (12/04/2003)
       if(taxes.isEmpty())
       {
           taxes.put("","");
       }
       */

       return taxes;
    }

    public static ArrayList loadTermsAndConditionsAsList() throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       ArrayList termsList = new ArrayList();
       TermsAndConditions terms = null;

       try {

         conn = DataSource.getConnection();

         //pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.taxAndTerms.loadSalesTaxes"));
		 //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings

         //pstmt = conn.prepareCall("SELECT * From Terms_And_Conditions");
		 pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.taxAndTerms.loadTermsAndConditionsAsList"));
         rs = pstmt.executeQuery();

         while(rs.next())
         {
            terms = new TermsAndConditions();
            terms.setTermsId(rs.getInt("Terms_ID"));
            terms.setTermsTitle(rs.getString("Terms_Title"));
            terms.setTerms(rs.getString("Terms"));
            termsList.add(terms);
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }
       return termsList;
    }

    public static String loadTermsAndConditions() throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       String terms = "";

       try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.taxAndTerms.loadTermsAndConditions"));
         rs = pstmt.executeQuery();

         if(rs.next())
         {
             terms = rs.getString("terms");
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }

       return terms;
    }

    public static void saveTermsAndConditions(String terms) throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         conn = DataSource.getConnection();

         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.deleteTerms"));
         pstmt.executeUpdate();
         pstmt.close();
         pstmt = null;

         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.insertTerms"));
         pstmt.setString (1, terms);
         pstmt.executeUpdate();

         success = true;

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
           try {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
    }

    public static void saveTermsAndConditions(String[] termsIds,String[] titles, String[] terms) throws ScalarException {
       Connection conn = null;
       CallableStatement cstmt = null;
       PreparedStatement pstmt=null;
       boolean success = false;

       try {

         conn = DataSource.getConnection();
        //Commented by Sarada on 22 August 2003 to move queries into SQL Query mappings
         //cstmt = conn.prepareCall("{Call sp_save_terms_and_conditions(?,?,?)}");
		   cstmt = conn.prepareCall(SqlQueryMappings.get ("sales.taxAndTerms.saveTermsAndConditions"));
           pstmt=conn.prepareStatement(SqlQueryMappings.get("sales.taxAndTerms.deleteTaxAndTerm"));

         for (int i=0; i<termsIds.length; i++)
         {
             if((titles[i] != null && titles[i].length() != 0) && (terms[i] != null && terms[i].length() != 0)) {
                cstmt.setInt (1, Integer.parseInt(termsIds[i]));
                cstmt.setString (2, titles[i]);
                cstmt.setString (3, terms[i]);
                cstmt.execute();
             }          //Added by Rohan on August 25th 2003 for removing those terms where title and term is empty
             else if((titles[i] == null || titles[i].length() == 0) && (terms[i] == null || terms[i].length() == 0))
             {
                 pstmt.setInt(1,Integer.parseInt(termsIds[i]));
                 pstmt.execute();
             }
         }

         success = true;

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
           try {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
    }

      // Modified by Veeru on 12th Sep 2004
      public static String saveSalesTaxes(DBTransaction dbTransaction, String[] states, String[] tax, String[] oldStates, String[] oldTax, String[] type ) throws ScalarException {

	       Connection conn = null;
	       PreparedStatement pstmt1 = null, pstmt2 = null;
           CallableStatement cstmt = null;
	       boolean success = false;
           String error_code = "";

	       try {

             if (dbTransaction == null) {
                    conn = DataSource.getConnection();
             }
             else {
                    conn = dbTransaction.getConnection();
             }

             /*
	         conn = DataSource.getConnection();
	         pstmt1 = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.deleteTaxes"));
	         pstmt1.executeUpdate();
	         pstmt1.close();
	         pstmt1 = null;

	         pstmt2 = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.insertTaxes"));
	         for (int i=0; i<states.length; i++)
	         {
	             if((states[i] != null && states[i].length() != 0) && (tax[i] != null && tax[i].length() != 0)) {
	                pstmt2.setString (1, states[i]);
	                pstmt2.setFloat (2, Float.parseFloat(tax[i]));
	                pstmt2.addBatch();
	             }
	         }

	         pstmt2.executeBatch();
             pstmt2.close();
             pstmt2 = null;
             */

             for (int i = 0; i < states.length && !error_code.equals("FAIL");  i++) {
                 if (  ( type[i].equals("new") && !"".equals( states[i] ) && !"0.0".equals( tax[i] )) || ( type[i].equals("old") && ( !( states[i].trim().equals(oldStates[i]) ) || !( tax[i].trim().equals(oldTax[i]) )) )) {
                     cstmt = conn.prepareCall(SqlQueryMappings.get("sales.taxAndTerms.changeTaxes"));
                     cstmt.setString(1, states[i] );
                     cstmt.setString(2, oldStates[i].trim() );
                     cstmt.setFloat(3, Float.parseFloat( tax[i].trim() ));
                     cstmt.setFloat(4, Float.parseFloat( oldTax[i].trim() ));
                     cstmt.setString(5, type[i].trim());
                     cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
                     cstmt.executeQuery();
                     error_code = cstmt.getString(6);
                 }
             }
	       } catch (Exception se) {
	              // Exception while updating user profile
	              // log it
	              se.printStackTrace();
				  throw new ScalarException ("Exception in saveSalesTaxes()"+se);

	       } finally {
	           try {
	                if ( !error_code.equals("FAIL") )
                    {
	                    conn.commit();
                    }
	                else
                    {
	                    conn.rollback();
                    }
	           }
	           catch (SQLException ignore) {}

	            if (cstmt != null) {
	                try { cstmt.close(); } catch (SQLException ignore){}
	            }
	            if (conn != null) {
	                try { conn.close(); } catch (SQLException ignore){}
	            }
	       }
		   return error_code;
    }




    public static void saveSalesTaxes(String[] states, String[] tax, String S) throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       boolean success = false;

       try {

         conn = DataSource.getConnection();

        // pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.deleteTaxes"));
        // pstmt.executeUpdate();
        // pstmt.close();
         //pstmt = null;

         pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.taxAndTerms.insertTaxes"));
         for (int i=0; i<states.length; i++)
         {
             if((states[i] != null && states[i].length() != 0) && (tax[i] != null && tax[i].length() != 0)) {
                pstmt.setString (1, states[i]);
                pstmt.setFloat (2, Float.parseFloat(tax[i]));
                pstmt.addBatch();
             }
         }

         pstmt.executeBatch();
         success = true;

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
           try {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
    }

    public static ArrayList loadMarketingReasonCodes() throws ScalarException {
        return loadCodes(loadReasonCodes("MARKETING"));
    }

    public static ArrayList loadOpsReasonCodes() throws ScalarException {
        return loadCodes(loadReasonCodes("OPERATIONS"));
    }

    public static ArrayList loadSalesReasonCodes() throws ScalarException {
        return loadCodes(loadReasonCodes("SALES"));
    }

    public static ArrayList loadFinanceReasonCodes() throws ScalarException {
        return loadCodes(loadReasonCodes("FINANCE"));
    }

    private static ArrayList loadCodes(ArrayList list) throws ScalarException {
        ArrayList codeList = new ArrayList();
        //Loop thru the tax list and show the values
        for(int i=0; i<list.size(); i++)
        {
              ReasonCode codes = (ReasonCode)list.get(i);
              String codeNo = codes.getCodeNo();
              String codeName = codes.getCodeName();

              codeList.add(new org.apache.struts.util.LabelValueBean(codeNo,codeNo));
         }

         return codeList;
    }

    public static ArrayList loadReasonCodes(String codeType) throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       ArrayList taxes = null;

       try {

         conn = DataSource.getConnection();

         if(codeType == null) {
            pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.reasonCodes.loadAllReasonCodes"));
         } else {
            pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.reasonCodes.loadReasonCodes"));
            pstmt.setString(1, codeType);
         }

         rs = pstmt.executeQuery();
         taxes = new ArrayList();
         ReasonCode codes = null;
         while(rs.next())
         {
             codes = new ReasonCode(rs.getString("reason_code_no"), rs.getString("reason_code_name"), rs.getString("reason_code_type"));
             taxes.add(codes);
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {

            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }

       }


       return taxes;
    }
    // Modified by Veeru on 10th Sep 2004
    //public static void saveReasonCodes(String[] codeId, String[] codes, String[] names, String[] types) throws ScalarException {
    public static String saveReasonCodes( DBTransaction dbTransaction, String[] codeId, String[] codes, String[] names, String[] types, String[] oldCodes, String[] oldNames, String[] oldTypes,String[] isNew) throws ScalarException {
       Connection conn = null;
       PreparedStatement pstmt = null;
       CallableStatement cstmt = null;
       String error_code = "";
       boolean success = false;

       try {

         // Added by Veeru on 10th Sep 2004

         ArrayList itemsToInsert = new ArrayList();
         ArrayList itemsToUpdate = new ArrayList();

         for (int i = 0; i < codes.length; i++) {
             if ( ( isNew[ i ] ).equalsIgnoreCase("yes") )
             {
                 itemsToInsert.add( i+"" );
             }
             else if ( !( codes[i].trim().equals(oldCodes[i]) ) || !( types[i].trim().equals(oldTypes[i]) ) || !( names[i].trim().equals(oldNames[i]) ))
             {
                 itemsToUpdate.add( i+"");
             }
         }
         if( dbTransaction == null )
         {
             conn = DataSource.getConnection();
         }
         else
         {
             conn = dbTransaction.getConnection();
         }
         Iterator iterator = itemsToInsert.iterator();
         while ( iterator.hasNext() ) {
               int i = Integer.parseInt( (String)iterator.next() );
               if ( ( codes[i] != null && codes[i].length () != 0 ) && ( names[i] != null && names[i].length () != 0 ) )
               {
                   pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.reasonCodes.insertReasonCodes" ) );
                   pstmt.setInt     ( 1, Integer.parseInt ( codes[i] ) );
                   pstmt.setString  ( 2, names[i] );
                   pstmt.setString  ( 3, types[i] );
                   pstmt.executeUpdate ();
                   pstmt.close ();
                   pstmt = null;
               }
         }
         error_code = "SUCCESS";
         iterator = itemsToUpdate.iterator();
         while ( iterator.hasNext() && "SUCCESS".equalsIgnoreCase(error_code) ) {
             int i = Integer.parseInt( (String)iterator.next() );
             if("".equals(codes[i].trim()))
             {
                codes[i] = "0";
             }
             cstmt = conn.prepareCall( SqlQueryMappings.get ( "sales.reasonCodes.updateDeleteReasonCodes" ));
             cstmt.setInt(1, Integer.parseInt( oldCodes[i] ) );
             cstmt.setInt(2, Integer.parseInt ( codes[i].trim()) );
             cstmt.setString(3, names[i].trim());
             cstmt.setString(4, types[i].trim());
             cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
             cstmt.executeQuery();
             error_code = cstmt.getString(5);
         }
         success = true;

         /*Modified by Meera on May 22 2003.
           Since there is no unique key in hold_reason_codes_table the data gets duplicated when
           updating the reason codes. So this round about way.
         */
         /* Commented by Veeru on 10th Sep 2004
		 pstmt = conn.prepareStatement ("Delete from hold_reason_codes_table");
		 pstmt.execute();
		 pstmt.close();
		 pstmt = null; */

         // Commented by Prabhanjan on 26th Jan 2004
         /* for (int i=0; i<codeId.length; i++)
         {
             if ((codeId[i]!=null && !"-1".equals(codeId[i].trim())) && (codes[i] != null && codes[i].length() == 0)) { //Added by Rohan for deleting empty reason codes
                pstmt  = conn.prepareStatement (SqlQueryMappings.get ("sales.reasonCodes.deleteReasonCodes"));
                pstmt.setString (1, codeId[i]);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
             }

             else if ((codeId[i]!=null && !"-1".equals(codeId[i].trim())) && (codes[i] != null && codes[i].length() != 0) && (names[i] != null && names[i].length() != 0)) {
                 pstmt  = conn.prepareStatement (SqlQueryMappings.get ("sales.reasonCodes.insertReasonCodes"));
                 pstmt.setString(1, codes[i]);
                 pstmt.setString(2, names[i]);
                 pstmt.setString(3, types[i]);
               //pstmt.setString (4, codeId[i]);
                 pstmt.executeUpdate();
                 pstmt.close();
                 pstmt = null;
             }

             else if ((codeId[i] != null || "-1".equals(codeId[i])) && (codes[i] != null && codes[i].length() != 0) && (names[i] != null && names[i].length() != 0)) {
                 pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.reasonCodes.insertReasonCodes"));
                 pstmt.setInt(1, Integer.parseInt(codes[i]));
                 pstmt.setString(2, names[i]);
                 pstmt.setString(3, types[i]);
                 pstmt.executeUpdate();
                 pstmt.close();
                 pstmt = null;
             }
         }
         */
           // Added by Prabhanjan on 23rd Jan 2004.
           // The previous code creates duplicate reason codes when updated.
           // So the updation is totally removed and everytime reason codes are modified, all the reason codes are deleted and newly added.


           /* Commented by Veeru on 10th Sep 2004
           pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.reasonCodes.deleteReasonCodes" ) );
           pstmt.executeUpdate ();
           pstmt.close ();
           pstmt = null;

           for ( int i = 0; i < codes.length; i++ )
           {
               if ( ( codes[i] != null && codes[i].length () != 0 ) && ( names[i] != null && names[i].length () != 0 ) )
               {
                   pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.reasonCodes.insertReasonCodes" ) );
                   pstmt.setInt     ( 1, Integer.parseInt ( codes[i] ) );
                   pstmt.setString  ( 2, names[i] );
                   pstmt.setString  ( 3, types[i] );
                   pstmt.executeUpdate ();
                   pstmt.close ();
                   pstmt = null;
               }
           } */
           // End of commented by veeru
           // end of additions by prabhanjan on 26th Jan 2004

           success = true;

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
              throw new ScalarException ("Exception in saveReasonCodes()"+se);

       } finally {
           try {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
        return error_code;
    }

   public static void updateSalesAuditTrail(DBTransaction dbTransaction, int salesOrderId, String userId, String action,
                        String comments) throws ScalarException
   {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int addressId = -1;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.auditTrail.insertInfo"));
			pstmt.setInt (1, salesOrderId);
			//Added  by sarada on 12th Sep 2003 to get Proper labels in Audit trail like Comments and Approer Comments
			if( comments != null)
            {
				action = action.trim() +"^^^"+comments;
            }
            pstmt.setString (2, userId);
            pstmt.setString (3, action);
            pstmt.setString (4, " ");

            pstmt.executeUpdate();
            success = true;

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert audit trail into database", sqle);
        }
        finally
        {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static ArrayList loadSalesAuditTrail(String orderId) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       ArrayList salesAudit = new ArrayList();
       int salesOrderId = -1;

       try {

         if(orderId != null)
             salesOrderId = Integer.parseInt(orderId);

         conn = DataSource.getConnection();

         pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadSalesAuditTrail"));
         pstmt.setInt(1, salesOrderId);

         rs = pstmt.executeQuery();

         SalesAuditTrail auditTrail = null;

         while(rs.next())
         {
                /*auditTrail = new SalesAuditTrail(rs.getString("User_ID"),
                            rs.getString("Actions"),
                            rs.getString("Comments"),
                            rs.getDate("Timestamp"));*/
            //Modified by sarada on 5th Jan  2004 to get Time along with Date in required format
             auditTrail = new SalesAuditTrail(rs.getString("User_ID"),
                            rs.getString("Actions"),
                            rs.getString("Comments"),
                            rs.getString("Timestamp"));
             salesAudit.add(auditTrail);
         }
       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }
       return salesAudit;
    }

    //This  code needs to replced as it not GOOD ;)
    public static String loadActionsPeformed(DBTransaction dbTransaction, String[] lineItemIds, String[] marketingHolds,
                            String[] financeHolds, String[] operationsHolds, String[] salesHolds,int salesOrderId , String[] scheduleDates) throws ScalarException
    {
           Connection conn = null;
           PreparedStatement pstmt = null;
           ResultSet rs = null;
           String actionsTaken = "";
           boolean flag = false;
           HashMap map = SalesOrderDAO.loadOrderLineItemsHashMap( dbTransaction, salesOrderId );
           //String salesOrderId = null;
		try
		{
         for(int i=0; i<lineItemIds.length; i++)
         {

            if(marketingHolds != null)
            {
                 actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"MARKETING",marketingHolds[i],lineItemIds[i]);
            }

            if(salesHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"SALES",salesHolds[i],lineItemIds[i]);
            }

            if(operationsHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"OPERATIONS",operationsHolds[i],lineItemIds[i]);
            }

            if(financeHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"FINANCE",financeHolds[i],lineItemIds[i]);
            }
            LineItem oldItem = ( LineItem )map.get( new Integer( lineItemIds[ i ] ) );
                if ( oldItem != null )
                {
                    String dbScheduleDate = oldItem.getScheduleDate();
                    if ( dbScheduleDate == null )
                    {
                        dbScheduleDate = "";
                    }
                    if( ! oldItem.getScheduleDate().equalsIgnoreCase( scheduleDates[i] ) )
                    {
                        if ( "".equals( dbScheduleDate ) )
                        {
                            actionsTaken = actionsTaken + "<br> Scheduled Ship Date  "+ scheduleDates[i] + "  is entered for Line item  " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) ;
                        }
                        else if ( "".equals( scheduleDates[i] ) )
                        {
                            actionsTaken = actionsTaken + "<br> Scheduled Ship Date is removed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) ;
                        }
                        else
                        {
                            actionsTaken = actionsTaken +"<br> Scheduled Ship Date  "+ dbScheduleDate +" is changed to  "+ scheduleDates[i] +"  for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) ;
                        }
                    }
                }
        }//for
      }catch(Exception e){e.printStackTrace();}
        return actionsTaken;
    }


    //Added by Sarada on 15th July 2003 to update modified holds through Sales Holds/Reason
    public static HashMap loadActionsPeformed(DBTransaction dbTransaction, String[] lineItemIds, String[] marketingHolds,
                           String[] financeHolds, String[] operationsHolds, String[] salesHolds,String s ) throws ScalarException
    {
           Connection conn = null;
           PreparedStatement pstmt = null;
           ResultSet rs = null;

        String actionsTaken = "";
        HashMap singleAction = new HashMap();
		String salesOrderId = null;
        for(int i=0; i<lineItemIds.length; i++)
         {
			 actionsTaken = "";

			 try
			 {
					 conn = DataSource.getConnection();
                      //commented by sarada on 22 august 2003 to move queries into sql query mappings

					 //pstmt = conn.prepareCall("SELECT distinct(SalesOrder_Id) FROM Sales_Hold_Table WHERE LineItem_ID = ?");
					   pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadActionsPeformed"));
					 pstmt.setInt(1, Integer.parseInt(lineItemIds[i]));
					 rs = pstmt.executeQuery();
					 if(rs.next())
					 {
						salesOrderId = rs.getString("SalesOrder_Id");
					 }
                       //System.out.println("salesOrderId "+salesOrderId);

					} catch (Exception se) {se.printStackTrace();}

			finally
            {
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore){}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
                }
                if(rs != null) {
                    try { rs.close(); } catch (SQLException ignore){}
                }
           }

            if(marketingHolds != null)
            {
                 actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"MARKETING",marketingHolds[i],lineItemIds[i]);
            }

            if(salesHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"SALES",salesHolds[i],lineItemIds[i]);
            }

            if(operationsHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"OPERATIONS",operationsHolds[i],lineItemIds[i]);
            }

            if(financeHolds != null)
            {
                actionsTaken = actionsTaken +  loadActionsTaken(dbTransaction,"FINANCE",financeHolds[i],lineItemIds[i]);
            }
           /* if(flag == true)
            {
                actionsTaken = actionsTaken + "<br> Line Item is Cancelled ";
            }*/

		   if(singleAction.containsKey(salesOrderId))
		   {
			  actionsTaken = (String) singleAction.get(salesOrderId) + actionsTaken ;
		   }
		   singleAction.put(salesOrderId,actionsTaken);

		   }

        return singleAction;
    }


    public static String loadActionsTaken(DBTransaction dbTransaction, String holdType, String holdValue, String lineItemId) throws ScalarException
    {
            int id = -1;
        int lineItemNo = 0;
        String dbHoldValue = null;

            if(lineItemId != null)
                id = Integer.parseInt(lineItemId);
            else
                 return null;
           try{
                     lineItemNo = loadLineItemNo(dbTransaction, id);

                    dbHoldValue = loadHoldValue(dbTransaction, holdType, id);
             }
             catch(ScalarException e){e.printStackTrace();}
            String actionTaken = "";
            //Added by Sarada on 14th July 2003 to display the holds 'X' instead of '0' in Audit Trail
              if((dbHoldValue != null) && (dbHoldValue.equalsIgnoreCase("0") ))
                dbHoldValue = "X";
            if ((holdValue != null) && ((holdValue.equalsIgnoreCase("0"))))
                holdValue = "X";

            if(((holdValue != null) && ("".equals(holdValue))) && ((dbHoldValue != null) && (!"".equals(dbHoldValue))))
            {
              actionTaken = holdType + " Reason code changed from : <b><I>" + dbHoldValue + "</I></b>  to  " + " <b><I>APPROVED </I></b> " +" for LineItem"+lineItemNo;

            }

            else
            if(((holdValue != null) && (!"".equals(holdValue))) && ((dbHoldValue != null) && ("".equals(dbHoldValue))))
               actionTaken = holdType + " Reason code set to <b><I>" + holdValue +"</I></b>  for LineItem "+lineItemNo + " ,the status has been changed from "+" <b><I>'Approved'</I></b>"+" to "+"<b><I>'Hold'</I></b>";
            else if(holdValue != null) {
                if(!dbHoldValue.equals(holdValue))
                   actionTaken = holdType + " Reason code changed from : <b><I>" + dbHoldValue + "</I></b> to <b><I> " +  holdValue +" </I></b> for LineItem"+lineItemNo;
                 }

            if(actionTaken.length() != 0)
                actionTaken = "<br>" + actionTaken;
                return actionTaken;

    }

    public static String loadHoldValue( DBTransaction dbTransaction, String holdType, int lineItemId ) throws ScalarException
    {
        Connection conn          = null;
        PreparedStatement pstmt  = null;
        ResultSet rs             = null;
        String holdValue         = null;

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            
            pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadHoldValue"));
            pstmt.setInt(1, lineItemId);
            pstmt.setString (2, holdType);

            rs = pstmt.executeQuery();
            if(rs.next())
            {
                holdValue = rs.getString("Hold_Reason_Code");
            }

        }
        catch (Exception se)
        {
              se.printStackTrace();
        }
        finally
        {
           if( rs != null )
           {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return holdValue;
    }

    //need to use this code for audit trail...presenlty i am still using the old code where DB utilisation is more
    public static HashMap loadHoldValues(DBTransaction dbTransaction, int salesOrderId) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       HashMap holds = new HashMap();
       try {

         if(dbTransaction != null)
             conn = dbTransaction.getConnection();
         else
            conn = DataSource.getConnection();
			 //commented by sarada on 22 august 2003 to move queries into sql query mappings

         //pstmt = conn.prepareCall("SELECT Hold_Reason_Code FROM Sales_Hold_Table WHERE SalesOrderId = ? ORDER By SalesOrderId");
		    pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadHoldValues"));
         pstmt.setInt(1, salesOrderId);

         rs = pstmt.executeQuery();

         while(rs.next())
         {
            holds.put(rs.getInt("LineItem_ID") + "_" + rs.getString("Hold_By_Group"), rs.getString("Hold_Reason_Code"));

         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }


       return holds;
    }


    public static int loadLineItemNo(DBTransaction dbTransaction, int lineItemId) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       int lineItemNo = -1;

       try {

         if(dbTransaction != null)
             conn = dbTransaction.getConnection();
         else
            conn = DataSource.getConnection();
            	 //commented by sarada on 22 august 2003 to move queries into sql query mappings
         //pstmt = conn.prepareCall("SELECT COOL_Line_Item_No FROM Customer_Open_Order_LineItem WHERE LineItem_ID = ?");
           pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadLineItemNo"));
         pstmt.setInt(1, lineItemId);

         rs = pstmt.executeQuery();
         if(rs.next())
         {
             lineItemNo = rs.getInt("COOL_Line_Item_No");
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }


       return lineItemNo;
    }

    public static int loadWafersNeededToStart(DBTransaction dbTransaction,String mktPartNumber,int qty) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       int wafersNeeded = -1;

       try {

         if(dbTransaction != null)
             conn = dbTransaction.getConnection();
         else
            conn = DataSource.getConnection();
       	 //commented by sarada on 22 august 2003 to move queries into sql query mappings
         //pstmt = conn.prepareCall("select FT_Gross_Die_Wafer from Fab_Table where ProcessStep_ID = (select ProcessStep_ID from fn_get_process_flow(?) where Process_Type_Name = 'FAB')");
		    pstmt = conn.prepareCall(SqlQueryMappings.get ("sales.auditTrail.loadWafersNeededToStart"));
         pstmt.setString(1, mktPartNumber);

         rs = pstmt.executeQuery();
         int grossDiesPerWafer  = 0;
         if(rs.next())
         {
             grossDiesPerWafer = rs.getInt("FT_Gross_Die_Wafer");
         }

         if(grossDiesPerWafer != 0)
            wafersNeeded = qty/grossDiesPerWafer;


       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }


       return wafersNeeded;
    }



    public static HashMap getReservedQuantityByWeek (String mktPartNumber, Inventory inventory) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap backLogByWeekMap = new HashMap();
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getBackLogByWeekByScheduleDate"));
            pstmt.setString (1, mktPartNumber);

            rset = pstmt.executeQuery();

            java.util.Date scheduleDate = null;
            Date now = new Date();
            int reqQty = 0;
            int lineItemNo = 0;
            String salesOrderNo = null;
            String customerName = null;
            long daysRemainingToScheduleDate = 0;
            long weekNumber = 0;
            ReservedQuantity reservedQty = null;
            java.util.Date resultDate = null;

            while(rset.next()) {
                resultDate = rset.getDate ("COOL_Date");
                if (resultDate == null) {
                    continue;
                }

                reqQty = rset.getInt ("COOL_Qty");
                lineItemNo = rset.getInt("COOL_Line_Item_No");
                salesOrderNo = rset.getString("SalesOrder_No");
                customerName = rset.getString("C_Name");

                daysRemainingToScheduleDate = ((resultDate.getTime() - now.getTime())/(1000*60*60*24));
                if (daysRemainingToScheduleDate < 0) {
                    weekNumber = 1;
                }
                else {
                    weekNumber = (daysRemainingToScheduleDate/7) + 1;
                }

                for (int i= (int) weekNumber; i>=1; i--) {
                    int currQty = inventory.getQtyByWeek(i);
                    int quantity = 0;
                    int totalQty = 0;

                    if(currQty == 0)
                        continue;

                    reservedQty = (ReservedQuantity) backLogByWeekMap.get (i+"");
                    if (reservedQty == null) {
                            reservedQty = new ReservedQuantity();
                            backLogByWeekMap.put (i+"", reservedQty);
                    }

                    if(reqQty < currQty) {
                        inventory.setQtyByWeek(i,currQty-reqQty);
                        quantity = reqQty;
                    } else {
                        reqQty = reqQty - currQty;
                        inventory.setQtyByWeek(i,0);
                        if((i == 1) && (reqQty > 0)) {
                        }
                        quantity = currQty;
                    }

                    totalQty = reservedQty.getQuantity();
                    reservedQty.setQuantity (quantity+totalQty);
                    reservedQty.setLineItemQuantityAndschDates (salesOrderNo, lineItemNo, quantity, StringUtils.formatDateToString(resultDate), customerName);
                }
            }


            success = true;
        }

        catch (Exception se) {
            se.printStackTrace();
            throw new ScalarException ("Exception while trying to get lot quantity for Mkt Partnumber: " + mktPartNumber, se);
        }

        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }

        return backLogByWeekMap;
    }



    public static void main(String[] args) throws ScalarException {

        /*String[] lineItemIds = {"136","138"};
        String[] scheduleDate = {"10/20/2002","10/25/2002"};
        String[] holds = {"1","0"};
        int salesOrderId = 90;

        approveSalesOrder(null,salesOrderId,lineItemIds,scheduleDate,holds,null);

        System.out.println("Executed and Approved " ); */
        /*System.out.println("LineItem Holds are = " + loadSuggScheduleDate("SS5050-88",20000));
        System.out.println("LineItem Holds are = " + loadShippableUnits("ss2526-88"));
        System.out.println("LineItem Holds are = " + loadScheduleAndRequestedDateUnits("ss2526-88"));
        System.out.println("LineItem Holds are = " + loadAllScheduleUnits("ss2526-88"));
        */

        //getLotQuantityByWeek ("MSP2000-CB-A1", ENTIRE_LOT_QUANTITY);
        //loadDeltaScheduleUnits ("MSP2000-CB-A1");

        getReservedQuantityByWeek("MSP-2000-CB-A1",getLotInventory ("MSP2000-CB-A1",ENTIRE_LOT_QUANTITY, "Shippable Units"));

        loadStatus(null,1088);
        //getReservedQuantityByWeek("MSP-2000-CB-A1",getLotInventory ("MSP2000-CB-A1",ENTIRE_LOT_QUANTITY, "Shippable Units"));

        //getBackLogByWeek ("MSP2000-CB-A1", BY_SCHEDULE_DATE);
        //getBackLogByWeek ("MSP2000-CB-A1", BY_REQUESTED_DATE);
    }

	// Added by vasanth 06/16/2003 ..
// For returning the Status of a particular SalesOrder
   public static String getSalesOrderStatus(DBTransaction dbTransaction, String marketingPartNo, String customerId, String salesOrderNo, String status, int isSample, String endUser, int year, String userId) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String infoStatus = "";
        try {
             // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getSalesOrderStatus"));
            pstmt.setString (1, marketingPartNo);
			pstmt.setString (2, customerId);
			pstmt.setString (3, salesOrderNo);
			pstmt.setString (4, status);
            pstmt.setInt (5, isSample);
            pstmt.setString(6, endUser);
            pstmt.setInt(7, year);
            pstmt.setString ( 8, userId );

            rset = pstmt.executeQuery();
            while (rset.next()) {
                infoStatus  = rset.getString("Status");
                //System.out.println( "infoStatus in dao = " + infoStatus);

            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get the result set from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
		//System.out.println("Result is "+ status);
        return infoStatus;
   }




    // SharatH on 16th Julu 03.
    /*
     * get the search String
     *
     */
    // Modified by Basha (08/09/2003)
    private static String loadSearchString(String salesOrderId, String customerId,String manufacturingPartNo , int isSample,String byDate, int year, String customerPO)
                             throws ScalarException
    {
        String search_string = "";

        /*
        System.out.println("---------------------------");
        System.out.println("SalesDAO");
        System.out.println("---------------------------");
        System.out.println("salesOrderId         :"+salesOrderId);
        System.out.println("customerId           :"+customerId);
        System.out.println("manufacturingPartNo  :"+manufacturingPartNo);
        System.out.println("isSample               :"+isSample);
        System.out.println("byDate               :"+byDate);
        System.out.println("---------------------------");
        */


        if(isSample == 0)
            search_string = " and So_Is_Sample = 0";
        else
            search_string = " and So_Is_Sample = 1";

        if((salesOrderId != null) && (salesOrderId.trim().length() != 0) && !("0".equalsIgnoreCase(salesOrderId.trim())))
            search_string = search_string + " and Sales_Order.SalesOrder_No like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

        if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
             search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);
             // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(manufacturingPartNo);

        if((customerId != null) && (!"0".equals(customerId)))
            search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);

        if((customerPO != null) && (customerPO.trim().length() != 0) && !("0".equalsIgnoreCase(customerPO.trim())))
                    search_string = search_string + " and Sales_Order.SO_Customer_PO_Number like '"+ StringUtils.formatToHandleQuote(customerPO.trim()) +"%'";

        //SharatH on 16th July 03.
        if( "ByRequestedDate".equalsIgnoreCase( byDate ) )
        {
              // search_string = " and COOL_Requested_Date < CURRENT_TIMESTAMP"; // Commented by Basha
              search_string = search_string + " and COOL_Requested_Date < CURRENT_TIMESTAMP"; // Added by Basha (08/09/2003)
        }
        else //( "ByScheduleDate".equalsIgnoreCase( byDate ) )
        {
             // search_string = " and COOL_Schedule_Date < CURRENT_TIMESTAMP"; // Commented by Basha
             search_string = search_string + " and ISNULL( COOL_Revised_Date, COOL_Schedule_Date ) < CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101 )"; // Added by Basha (08/09/2003)
        }

        if(year != 0)
        {
            search_string = search_string + " and year(Sales_Order.So_Booking_Date) = " + year;
        }

         return search_string + " ORDER BY SalesOrder_No";

    }

    //Added by Rohan on August 19th 2003
    //This method will return a boolean value indicating whether any line items were found with the given search criteria
    //Added year parameter by Prabhanjan on 09 Mar 2004
    public static boolean doesLineItemExist(String salesOrderId,String customerId, String manufacturingPartNo, int isSample, int year
                                           ) throws ScalarException {

           Connection conn = null;
           PreparedStatement pstmt1 = null, pstmt2 = null;
           ResultSet rset = null;
           boolean searchStatus=false;
           int lineItemCount=0;
           String searchString = null;

           try {
               searchString = loadSearchStringForLineItemSearch(salesOrderId,customerId,manufacturingPartNo,isSample, year);
               conn = DataSource.getConnection();

               pstmt1 = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.COOLCountForSearchCriteria") + searchString );
               // Prabhanjan, Aug 2, 2004
               if(conn == null || conn.isClosed() )
               {
                   conn = DataSource.getConnection();
               }
               pstmt2 = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.CCOLCountForSearchCriteria") + searchString );

               rset = pstmt1.executeQuery();
               if(rset.next())
                   lineItemCount=lineItemCount+rset.getInt(1);

               if( rset != null ) rset.close();
               if( pstmt1 != null ) pstmt1.close();
               rset = null;
               pstmt1 = null;

               rset = pstmt2.executeQuery();

               if(rset.next())
                   lineItemCount=lineItemCount+rset.getInt(1);

               if(lineItemCount == 0)
                   searchStatus=false;
               else
                   searchStatus=true;

               if( rset != null ) rset.close();
               if( pstmt2 != null ) pstmt2.close();
               rset = null;
               pstmt2 = null;

           }
           catch (Exception sqle) {
               searchStatus=false;
               sqle.printStackTrace();
               throw new ScalarException ("Could not check the COOL and CCOL tables", sqle);
           }
           finally {
               if (rset != null) {
                   try { rset.close(); } catch (SQLException ignore) {}
               }
               if (pstmt1 != null) {
                   try { pstmt1.close(); } catch (SQLException ignore) {}
               }
               if (pstmt2 != null) {
                   try { pstmt2.close(); } catch (SQLException ignore) {}
               }

               if (conn != null) {
                   try { conn.close(); } catch (SQLException ignore) {}
               }
           }

           return searchStatus;
      }


    private static String loadSearchStringForLineItemSearch(String salesOrderId, String customerId, String manufacturingPartNo, int isSample)
                            throws ScalarException
    {
       /*
       System.out.println("Begin:SalesDAO");
       System.out.println("SalesOrderId:"+salesOrderId);
       System.out.println("CustomerId:"+customerId+"End");
       System.out.println("MarketingPartNo:"+manufacturingPartNo);
       System.out.println("End:SalesDAO");
       */

       String search_string = "";
       if(isSample == 0)
           search_string = " and So_Is_Sample = 0";
       else
           search_string = " and So_Is_Sample = 1";

       if((salesOrderId != null) && (salesOrderId.trim().length() != 0))
            search_string = search_string + " and Sales_Order.SalesOrder_No like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

       //if((manufacturingPartNo != null) && (!manufacturingPartNo.trim().equals("0")) && (manufacturingPartNo.trim().length() > 0))
       if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);
            // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(manufacturingPartNo);

       if((customerId != null) && (!"0".equals(customerId) && (customerId.length() > 0)))
           search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);


       return search_string ;

    }
    //End of additions by Rohan on August 19th 2003

    private static String loadSearchStringForLineItemSearch(String salesOrderId, String customerId, String manufacturingPartNo, int isSample, int year)
                            throws ScalarException
    {

       //System.out.println("Begin:SalesDAO");
       //System.out.println("SalesOrderId:"+salesOrderId);
       //System.out.println("CustomerId:"+customerId+"End");
       //System.out.println("MarketingPartNo:"+manufacturingPartNo);
       //System.out.println("End:SalesDAO");


       String search_string = "";
       if(isSample == 0)
           search_string = " and So_Is_Sample = 0";
       else
           search_string = " and So_Is_Sample = 1";

       if((salesOrderId != null) && (salesOrderId.trim().length() != 0))
            search_string = search_string + " and Sales_Order.SalesOrder_No like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

       //if((manufacturingPartNo != null) && (!manufacturingPartNo.trim().equals("0")) && (manufacturingPartNo.trim().length() > 0))
       if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);
            // search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductId(manufacturingPartNo);

       if((customerId != null) && (!"0".equals(customerId) && (customerId.length() > 0)))
           search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);

       if(year != 0)
       {
           search_string = search_string + " and year(Sales_Order.So_Booking_Date) = " + year;
       }

       return search_string;
    }


    //Added by Rohan on August 21st 2003
    // This method will return the summary of actions performed on the holds of the line items of each sales orderID.
    //The map will contain the sales order Id and the change cummary corresponding to it

    public static HashMap loadActionsPeformedSummary(DBTransaction dbTransaction,String[] lineItemIds, String[] marketingHolds,
                            String[] financeHolds, String[] operationsHolds, String[] salesHolds,String[] salesOrderIds) throws ScalarException
    {
        String actionTaken="";
        HashMap actionsPerformedSummary = new HashMap();

        //Creating a Hash Map of the sales orders
        for(int i=0;i<salesOrderIds.length;i++)
        {
            actionsPerformedSummary.put(salesOrderIds[i],new StringBuffer());
        }
		try
		{
            for(int i=0; i<salesOrderIds.length; i++)
            {
                if(isLineItemApproved(dbTransaction,Integer.parseInt(lineItemIds[i])))
                {
                    if(!((marketingHolds[i]!=null && "".equals(marketingHolds[i].trim())) && (financeHolds[i]!=null && "".equals(financeHolds[i].trim())) && (operationsHolds[i]!=null && "".equals(operationsHolds[i].trim())) && (salesHolds[i]!=null && "".equals(salesHolds[i].trim()))))
                     {
                        ((StringBuffer)actionsPerformedSummary.get(salesOrderIds[i])).append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been moved to hold from approved");
                     }
                }
                else
                {
                    if((marketingHolds[i]!=null && "".equals(marketingHolds[i].trim())) && (financeHolds[i]!=null && "".equals(financeHolds[i].trim())) && (operationsHolds[i]!=null && "".equals(operationsHolds[i].trim())) && (salesHolds[i]!=null && "".equals(salesHolds[i].trim())))
                    {
                        if(isLineItemCancelledButNotApproved(dbTransaction,Integer.parseInt(lineItemIds[i])))
                            ((StringBuffer)actionsPerformedSummary.get(salesOrderIds[i])).append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been cancelled and approved");
                        else
                            ((StringBuffer)actionsPerformedSummary.get(salesOrderIds[i])).append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been approved");
                     }
                    else //Added by Rohan on september 16th 2003 to add the comment if the holds are changes
                    {
                        if((salesHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"SALES",salesHolds[i],lineItemIds[i]))) || (operationsHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"OPERATIONS",operationsHolds[i],lineItemIds[i]))) || (marketingHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"MARKETING",marketingHolds[i],lineItemIds[i]))) || (financeHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"FINANCE",financeHolds[i],lineItemIds[i]))))
                        {
                            ((StringBuffer)actionsPerformedSummary.get(salesOrderIds[i])).append("<br> Holds changed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])));
                        }
                    }
                    //End of aditions by Rohan on September 16th 2003
                }
            }//for
        }catch(Exception e){e.printStackTrace();}

        return actionsPerformedSummary;
    }
    public static String loadActionsPeformedSummary(DBTransaction dbTransaction, String[] lineItemIds, String[] marketingHolds,
                            String[] financeHolds, String[] operationsHolds, String[] salesHolds ) throws ScalarException
    {
        StringBuffer actionsTakenSummary = new StringBuffer("");
		try
		{
            for(int i=0; i<lineItemIds.length; i++)
            {
                if(!isLineItemApproved(dbTransaction,Integer.parseInt(lineItemIds[i])))
                {
                    if((marketingHolds[i]!=null && "".equals(marketingHolds[i].trim())) && (financeHolds[i]!=null && "".equals(financeHolds[i].trim())) && (operationsHolds[i]!=null && "".equals(operationsHolds[i].trim())) && (salesHolds[i]!=null && "".equals(salesHolds[i].trim())))
                    {
                        if(isLineItemCancelledButNotApproved(dbTransaction,Integer.parseInt(lineItemIds[i])))
                            actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been cancelled and approved");
                        else
                            actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been approved");
                    }
                    else //Added by Rohan on september 16th 2003 to add the comment if the holds are changes
                    {
                        if((salesHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"SALES",salesHolds[i],lineItemIds[i]))) || (operationsHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"OPERATIONS",operationsHolds[i],lineItemIds[i]))) || (marketingHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"MARKETING",marketingHolds[i],lineItemIds[i]))) || (financeHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"FINANCE",financeHolds[i],lineItemIds[i]))))
                        {
                            actionsTakenSummary.append("<br> Holds changed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])));
                        }
                    }
                    //End of aditions by Rohan on September 16th 2003
                }
                else //Added by Rohan on september 16th 2003 to add the comment if the holds are changes
                {
                    if(!((marketingHolds[i]!=null && "".equals(marketingHolds[i].trim())) && (financeHolds[i]!=null && "".equals(financeHolds[i].trim())) && (operationsHolds[i]!=null && "".equals(operationsHolds[i].trim())) && (salesHolds[i]!=null && "".equals(salesHolds[i].trim()))))
                    {
                        actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been moved to hold from approved");
                    }
                }
                //End of aditions by Rohan on September 16th 2003
            }//for
        }catch(Exception e){e.printStackTrace();}

        return actionsTakenSummary.toString();
    }
    // Added by Veeru on 18th April 2005
    public static String loadActionsPeformedSummary(DBTransaction dbTransaction, String[] lineItemIds, String[] marketingHolds,
                            String[] financeHolds, String[] operationsHolds, String[] salesHolds, String[] scheduleDates, int salesOrderId ) throws ScalarException
    {
        StringBuffer actionsTakenSummary = new StringBuffer("");
        HashMap map = SalesOrderDAO.loadOrderLineItemsHashMap( dbTransaction, salesOrderId );

		try
		{
            for( int i = 0 ; i < lineItemIds.length; i++ )
            {
                if( !isLineItemApproved( dbTransaction, Integer.parseInt( lineItemIds[i] ) ) )
                {
                    if( ( marketingHolds[i] != null && "".equals( marketingHolds[i].trim() ) ) && ( financeHolds[i] != null && "".equals( financeHolds[i].trim() ) ) && ( operationsHolds[i] != null && "".equals( operationsHolds[i].trim() ) ) && ( salesHolds[i] != null && "".equals( salesHolds[i].trim() ) ) )
                    {
                        if(isLineItemCancelledButNotApproved( dbTransaction,Integer.parseInt( lineItemIds[i] ) ) )
                            actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been cancelled and approved");
                        else
                            actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been approved");
                    }
                    else //Added by Rohan on september 16th 2003 to add the comment if the holds are changes
                    {
                        if((salesHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"SALES",salesHolds[i],lineItemIds[i]))) || (operationsHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"OPERATIONS",operationsHolds[i],lineItemIds[i]))) || (marketingHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"MARKETING",marketingHolds[i],lineItemIds[i]))) || (financeHolds[i]!=null && !"".equals(loadActionsTaken(dbTransaction,"FINANCE",financeHolds[i],lineItemIds[i]))))
                        {
                            actionsTakenSummary.append("<br> Holds changed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])));
                        }
                    }
                    //End of aditions by Rohan on September 16th 2003
                }
                else //Added by Rohan on september 16th 2003 to add the comment if the holds are changes
                {
                    if(!((marketingHolds[i]!=null && "".equals(marketingHolds[i].trim())) && (financeHolds[i]!=null && "".equals(financeHolds[i].trim())) && (operationsHolds[i]!=null && "".equals(operationsHolds[i].trim())) && (salesHolds[i]!=null && "".equals(salesHolds[i].trim()))))
                    {
                        actionsTakenSummary.append("<br> Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) + " has been moved to hold from approved");
                    }
                }
                //End of aditions by Rohan on September 16th 2003
                LineItem oldItem = ( LineItem )map.get( new Integer( lineItemIds[ i ] ) );
                if ( oldItem != null )
                {
                    String dbScheduleDate = oldItem.getScheduleDate();
                    if ( dbScheduleDate == null )
                    {
                        dbScheduleDate = "";
                    }
                    
                    if( ! oldItem.getScheduleDate().equalsIgnoreCase( scheduleDates[i] ) ) 
                    {
                        if ( "".equals( dbScheduleDate ) )
                        {
                            actionsTakenSummary.append("<br>Scheduled Ship Date is entered for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) );
                        }
                        else if ( "".equals( scheduleDates[i] ) )
                        {
                            actionsTakenSummary.append("<br>Scheduled Ship Date is removed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) );
                        }
                        else
                        {
                            actionsTakenSummary.append("<br>Scheduled Ship Date is changed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) );
                        }
                    }
                }
            }//for
        }catch(Exception e){e.printStackTrace();}

        return actionsTakenSummary.toString();
    }

    //This method will return true if the status of the line item is cancelled
    public static boolean isLineItemCancelledButNotApproved(DBTransaction dbTransaction, int lineItemId) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       boolean cancelledButNotApproved=false;

       try {

         if(dbTransaction != null)
             conn = dbTransaction.getConnection();
         else
            conn = DataSource.getConnection();

         pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.IsCancelStatus"));
         pstmt.setInt(1, lineItemId);
         rs = pstmt.executeQuery();
         if(rs.next())
         {
             if(rs.getInt(1)==0)
                cancelledButNotApproved=true;
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }
       return cancelledButNotApproved;
    }

    //This method will return if the line item is approved
    public static boolean isLineItemApproved(DBTransaction dbTransaction, int lineItemId) throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       boolean isApproved=false;

       try {

         if(dbTransaction != null)
             conn = dbTransaction.getConnection();
         else
            conn = DataSource.getConnection();

         pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.COOLStatus"));
         pstmt.setInt(1, lineItemId);
         rs = pstmt.executeQuery();
         if(rs.next())
         {
             if("APPROVED".equals(rs.getString(1)))
                isApproved=true;
         }

       } catch (Exception se) {
             // Exception while updating user profile
             // log it
              se.printStackTrace();
       } finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
       }
       return isApproved;
    }
    //End of  additions by Rohan on August 21st 2003
    //Added by Rohan on August 25th 2003
    public static int getSalesOrdersCount(String salesOrderId,String customerId, String manufacturingPartNo, int isSample
                                           ) throws ScalarException {

           Connection conn = null;
           PreparedStatement pstmt1 = null, pstmt2 = null;
           ResultSet rset = null;
           ArrayList salesOrderIds=new ArrayList();
           String tempSalesOrderId="";
           boolean salesOrderIdExists=false;

           try {
               String searchString = loadSearchStringForLineItemSearch(salesOrderId,customerId,manufacturingPartNo,isSample);
               conn = DataSource.getConnection();
               pstmt1 = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.DistinctOpenSalesOrderIdsMatchingSearchCriteria") + searchString );

               // Prabhanjan, Aug 2, 2004
               if(conn == null || conn.isClosed() )
               {
                   conn = DataSource.getConnection();
               }

               pstmt2 = conn.prepareStatement(SqlQueryMappings.get ("sales.salesOrder.DistinctCancelledSalesOrderIdsMatchingSearchCriteria") + searchString );

               rset = pstmt1.executeQuery();
               while(rset.next())
               {
                   salesOrderIds.add(String.valueOf(rset.getInt(1)));
               }
               if(rset != null) rset.close();
               if(pstmt1 != null) pstmt1.close();

               rset = pstmt2.executeQuery();
               while(rset.next())
               {
                    tempSalesOrderId=String.valueOf(rset.getInt(1));
                    for(int i=0;i<salesOrderIds.size();i++)
                    {
                        if(tempSalesOrderId.equals((String)salesOrderIds.get(i)))
                        {
                            salesOrderIdExists=true;
                            break;
                        }
                    }
                    if(!salesOrderIdExists)
                        salesOrderIds.add(tempSalesOrderId);
               }
               if(rset != null) rset.close();
               if(pstmt2 != null) pstmt2.close();

           }
           catch (Exception sqle) {
               sqle.printStackTrace();
               throw new ScalarException ("Could not get SalesOrder Count", sqle);
           }
           finally {
               if (rset != null) {
                   try { rset.close(); } catch (SQLException ignore) {}
               }
               if (pstmt1 != null) {
                   try { pstmt1.close(); } catch (SQLException ignore) {}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException ignore) {}
               }
           }

           return salesOrderIds.size();
      }
    //End of additions by Rohan on August 25th 2003

    //Added by Rohan on September 11th 2003
    //This method is used for inserting a comment for a sales order Id
    public static void insertAlarmComment(String comments, int salesOrderId, User user)
                        throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;


        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAlramId"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            if (rset.next()) {
                //No Alarm Id found.
                int alarmId = rset.getInt("Alarm_ID");

                //Load all the existing alarm
                //Alarm oldAlarm = AlarmDAO.loadAlarmById (alarmId);

                //Set the user response according to the approve/reject
                //if(oldAlarm==null)
                        //throw new ScalarException ("Could not get alarmId for the salesOrderId " + salesOrderId + " from database");

                //oldAlarm.addComment(null,new Comment(user.getUserName(),comments),true);
                AlarmDAO.insertComment (null, alarmId, new Comment(user.getUserId(),comments), Alarm.ALARM_STATUS_UPDATED, user.getUserName());
                conn.commit();
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for salesOrderId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

   }
    //End of additions bvy Rohan on September 11th 2003
    //Added by Rohan on September 16th 2003 for getting the revised date change summary
    public static String loadRevisedDateChangesSummary(DBTransaction dbTransaction, String[] lineItemIds, String[] revisedDates ) throws ScalarException
    {
        StringBuffer actionsTakenSummary = new StringBuffer("");
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String dbRevisedDate="";

		try
		{
            if(dbTransaction==null) {
                conn = DataSource.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadLineItemRevisedDate"));

            for(int i=0; i<lineItemIds.length; i++)
            {
                if(revisedDates[i]==null)   {
                    revisedDates[i]="";
                }
                pstmt.setInt (1, Integer.parseInt(lineItemIds[i]));
                rset = pstmt.executeQuery();
                if(rset.next())
                {
                    dbRevisedDate=StringUtils.formatDateToString(rset.getDate("COOL_Revised_Date"),null);
                    if(dbRevisedDate==null){
                        dbRevisedDate="";
                    }
                    if(!revisedDates[i].equals(dbRevisedDate))
                    {
                        if("".equals(dbRevisedDate)) {
                            actionsTakenSummary.append("<br>Revised Ship Date " + revisedDates[i] + " entered for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])));
                        }
                        else if ( "".equals( revisedDates[i] ) )
                        {
                            actionsTakenSummary.append("<br>Revised Ship Date is removed for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) );
                        }
                        else
                        {
                            actionsTakenSummary.append("<br>Revised Ship Date  "+ dbRevisedDate +" is changed to  "+ revisedDates[i] +"  for Line item " + loadLineItemNo(dbTransaction, Integer.parseInt(lineItemIds[i])) ) ;
                        }
                    }
                }
                else
                {
                    throw new ScalarException("Unable to load Revised date for the Line Item ");
                }
            }//for
        }catch(Exception e){e.printStackTrace();}
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return actionsTakenSummary.toString();
    }
    //End of additions by Rohan on September 16th 2003

  /* Added by  Sarada on 28th Nov 2003
   * To get lineitems that are cancelled when we approve through Pending Orders screen
   */
    public static ArrayList loadCancelValue(DBTransaction dbTransaction,String[] lineitemIds) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList cancelledlineitemIds = new ArrayList();
        int cancelValue = 0;
        boolean success = false;

        try {
             if(dbTransaction ==null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadCancelValue"));

            for(int i=0; i<lineitemIds.length; i++)
            {
                pstmt.setInt (1, Integer.parseInt(lineitemIds[i]));
                rset = pstmt.executeQuery();

                if (rset.next())
                {
                    cancelValue = rset.getInt("Total");
                    if(cancelValue == 1 )
                    {
                         cancelledlineitemIds.add(lineitemIds[i]);
                    }
                }
            }

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get value for lineitemId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
          }
        }
           return   cancelledlineitemIds;
   }
    /* Added by  Sarada on 28th Nov 2003
   * To get lineitems that are cancelled when we approve through Sales Holds/Release screen
   */
   public static HashMap loadAllCancelledLineitems(DBTransaction dbTransaction,String[] lineitemIds) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap cancelledlineitemIds = new HashMap();

        boolean success = false;
        int lineitemslength = lineitemIds.length;
        ArrayList cancelledLineitem = new ArrayList();
        String salesOrderId[] = new String[lineitemslength];
        String lineitemId[] = new String[lineitemslength];

        try {
             if(dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllCancelledLineitems"));
            for(int i=0; i<lineitemslength; i++)
            {
                pstmt.setInt (1, Integer.parseInt(lineitemIds[i]));
                rset = pstmt.executeQuery();

                if(rset != null)
                {
                    while (rset.next())
                    {
                        salesOrderId[i] = ""+ rset.getInt("SalesOrder_Id");
                        lineitemId[i] = ""+rset.getInt("LineItem_Id");

                        if(cancelledlineitemIds.containsKey(salesOrderId[i]))
                        {
                            cancelledLineitem = (ArrayList)cancelledlineitemIds.get(salesOrderId[i]);
                        }
                        else
                            cancelledLineitem = new ArrayList();

                         cancelledLineitem.add(lineitemId[i]);
                         cancelledlineitemIds.put(salesOrderId[i],cancelledLineitem);
                    }
                }
            }//for

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get value for lineitemId from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
          if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
          }
        }
           return   cancelledlineitemIds;
   }
   /* Added by  Sarada on 28th Nov 2003
    * To get lineitems that are not cancelled when we approve through Sales Holds/Release screen
    */
    public static HashMap loadAllRemainedLineitems(DBTransaction dbTransaction,String[] lineitemIds) throws ScalarException
       {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rset = null;
            HashMap remainedlineitemIds = new HashMap();

            boolean success = false;
            int lineitemslength = lineitemIds.length;
            ArrayList cancelledLineitem = new ArrayList();
            String salesOrderId[] = new String[lineitemslength];
            String lineitemId[] = new String[lineitemslength];

            try {
                 if(dbTransaction == null) {
                    conn = DataSource.getConnection();
                } else {
                    conn = dbTransaction.getConnection();
                }
                 pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadAllRemainedLineitems"));
                for(int i=0; i<lineitemslength; i++)
                {
                    pstmt.setInt (1, Integer.parseInt(lineitemIds[i]));
                    rset = pstmt.executeQuery();
		    if(rset != null)
                    {
                        while (rset.next())
                        {
                            salesOrderId[i] = ""+ rset.getInt("SalesOrder_Id");
                            lineitemId[i] = ""+rset.getInt("LineItem_Id");
                            if(remainedlineitemIds.containsKey(salesOrderId[i]))
                            {
                                cancelledLineitem = (ArrayList)remainedlineitemIds.get(salesOrderId[i]);
                            }
                            else
                                cancelledLineitem = new ArrayList();
                                cancelledLineitem.add(lineitemId[i]);
				remainedlineitemIds.put(salesOrderId[i],cancelledLineitem);

                        }
                    }
                }
            }
            catch (Exception sqle) {
                sqle.printStackTrace();
                throw new ScalarException ("Could not get value for lineitemId from database", sqle);
            }
            finally {
                if (rset != null) {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
              if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore) {}
              }
            }
               return   remainedlineitemIds;
       }


   /* Added by  Sarada on 28th Nov 2003
   *  To get the action Taken when we approve through Pending Orders screen
   */
    public static double[] loadActionValue(DBTransaction dbTransaction,int salesOrderId) throws ScalarException
     {
          Connection conn = null;
          PreparedStatement pstmt = null;
          ResultSet rset = null;
         // float newSalesTotal = 0.0f;
         // float newSalesTax = 0.0f;
		  double newSalesTotal = 0.0;
		  double newSalesTax = 0.0;
          double[] subTotalAndTax = new double[5];

          try {
               if(dbTransaction ==null) {
                  conn = DataSource.getConnection();
              } else {
                  conn = dbTransaction.getConnection();
              }

		pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadActionValue"));
		pstmt.setInt (1, salesOrderId);
		pstmt.setInt (2, salesOrderId);
		pstmt.setInt (3, salesOrderId);
		rset = pstmt.executeQuery();

                  while (rset.next())
                  {
                      newSalesTotal = rset.getDouble("SO_Sub_Total");
                      newSalesTax = rset.getDouble("SO_Sales_Tax");
                  }
                      subTotalAndTax[0] =  newSalesTotal;
                      subTotalAndTax[1] =  newSalesTax;

          }
          catch (Exception sqle) {
              sqle.printStackTrace();
              throw new ScalarException ("Could not get value for lineitemId from database", sqle);
          }
          finally {
                   if (rset != null) {
                  try { rset.close(); } catch (SQLException ignore) {}
              }
              if (pstmt != null) {
                  try { pstmt.close(); } catch (SQLException ignore) {}
              }
            if (conn != null) {
                  try { conn.close(); } catch (SQLException ignore) {}
            }
          }
             return   subTotalAndTax;
     }
  /* Added by  Sarada on 28th Nov 2003
   *  To get the action Taken when we approve through Sales Holds/Release  screen
   */
    public static HashMap loadAllActionValue(DBTransaction dbTransaction,String[] salesOrderId) throws ScalarException
         {
                Connection conn = null;
                PreparedStatement pstmt = null;
                ResultSet rset = null;

                HashMap totalAction = new  HashMap();
                int salesOrderIdlength = salesOrderId.length;

                float[] newSalesTotal = new float[salesOrderIdlength];
                float[] newSalesTax = new float[salesOrderIdlength];
                float[] oldSalesTotal = new float[salesOrderIdlength];
                float[] oldSalesTax = new float[salesOrderIdlength];
                String[] subTotalAndTax = new String[salesOrderIdlength];
                try {
                   if(dbTransaction ==null) {
                      conn = DataSource.getConnection();
                  } else {
                      conn = dbTransaction.getConnection();
                  }
                  pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadActionValue"));
                     for(int i=0; i<salesOrderIdlength; i++)
                     {
                          subTotalAndTax[i] = "";
                         //System.out.println(salesOrderId[i]);
                         pstmt.setInt (1, Integer.parseInt(salesOrderId[i]));
                          pstmt.setInt (2, Integer.parseInt(salesOrderId[i]));
                          pstmt.setInt (3, Integer.parseInt(salesOrderId[i]));
                          rset = pstmt.executeQuery();

                          while (rset.next())
                          {
                              newSalesTotal[i] = rset.getFloat("SO_Sub_Total");
                              newSalesTax[i] = rset.getFloat("SO_Sales_Tax");
                              oldSalesTotal[i] = rset.getFloat("CSO_Sub_Total");
                              oldSalesTax[i] = rset.getFloat("CSO_Sales_Tax");
                          }
                           subTotalAndTax[i] = subTotalAndTax[i] + newSalesTotal[i]+"@@@"+newSalesTax[i] ;
                          // System.out.println("subTotalAndTax "+subTotalAndTax[i]);

                           if(totalAction.containsKey(salesOrderId))
                           {
                              subTotalAndTax[i] = (String) totalAction.get(salesOrderId) + subTotalAndTax[i] ;
                           }

                         //  System.out.println("actionValue for salesOrderId"+actionValue + "for salesOrderId"+salesOrderId[i]);
	                     totalAction.put(salesOrderId[i],subTotalAndTax[i]);
                     }
              }
              catch (Exception sqle) {
                  sqle.printStackTrace();
                  throw new ScalarException ("Could not get value for lineitemId from database", sqle);
              }
              finally {
                       if (rset != null) {
                      try { rset.close(); } catch (SQLException ignore) {}
                  }
                  if (pstmt != null) {
                      try { pstmt.close(); } catch (SQLException ignore) {}
                  }
                if (conn != null) {
                      try { conn.close(); } catch (SQLException ignore) {}
                }
              }
                 return   totalAction;
         }


    /*To get all Shipping Terms */
    public static ArrayList getAllShippingTerms() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList shippingTerms = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getAllShippingTerms" ) );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                shippingTerms.add( rs.getString( "ShippingTerms" ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get Contact Persons from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippingTerms;
    }

    /*To insert Pay Terms */
    public static void insertShippingTerms( DBTransaction dbTransaction, String shippingTerms ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.insertShippingTerms" ) );
            pstmt.setString( 1, shippingTerms );
            pstmt.executeUpdate();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not insert shippingTerms, \"" + shippingTerms + "\" into database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
    }

    // Added by Veeru on 17th Jan 2005 to load all Delivery Terms
    public static ArrayList getAllDeliveryTerms( DBTransaction dbTransaction ) throws ScalarException {
        Connection conn = null;
        ResultSet rs= null;
        PreparedStatement pstmt = null;
        ArrayList deliveryTermsList = new ArrayList();
        boolean success = false;

        try {
                if ( dbTransaction == null ) {
                    conn = DataSource.getConnection();
                } else {
                    conn = dbTransaction.getConnection();
                }
                pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.getAllDeliveryTerms") );
                rs = pstmt.executeQuery();
                while( rs.next() ) {
                    deliveryTermsList.add( rs.getString("DELIVERY_TERMS") );
                }
                success = true;

            } catch( Exception sqle) {
                sqle.printStackTrace();
                throw new ScalarException ("Could not load DeliveryTerms from database"+ sqle);
            } finally {
                    if ( dbTransaction == null ){
                        try {
                                if ( conn != null ) {
                                    if ( success == true ) {
                                        conn.commit();
                                    }
                                    else {
                                        conn.rollback();
                                    }
                                }
                        } catch ( SQLException ignore ){}
                    }
                    if ( rs != null ) {
                        try{ rs.close(); } catch( SQLException ignore ){}
                    }
                    if ( pstmt != null ) {
                        try{ pstmt.close(); } catch( SQLException ignore ){}
                    }
                    if ( conn != null ) {
                        try{ conn.close(); } catch( SQLException ignore ){}
                    }
            }
        return deliveryTermsList;
    }
    // Added by Veeru on 17th Jan 2005 to load insert new Delivery Terms
    public static void insertDeliveryTerms( DBTransaction dbTransaction, String deliveryTerms ) throws ScalarException {
            Connection conn = null;
            ResultSet rs= null;
            PreparedStatement pstmt = null;
            boolean success = false;

            try {
                    if ( dbTransaction == null ) {
                        conn = DataSource.getConnection();
                    } else {
                        conn = dbTransaction.getConnection();
                    }
                    pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.insertDeliveryTerms") );
                    pstmt.setString( 1, deliveryTerms );
                    pstmt.executeUpdate();
                    success = true;

                } catch( Exception sqle) {
                    sqle.printStackTrace();
                    throw new ScalarException ("Could not save DeliveryTerms "+ sqle);
                } finally {
                        if ( dbTransaction == null ){
                            try {
                                    if ( conn != null ) {
                                        if ( success == true ) {
                                            conn.commit();
                                        }
                                        else {
                                            conn.rollback();
                                        }
                                    }
                            } catch ( SQLException ignore ){}
                        }
                        if ( pstmt != null ) {
                            try{ pstmt.close(); } catch( SQLException ignore ){}
                        }
                        if ( conn != null ) {
                            try{ conn.close(); } catch( SQLException ignore ){}
                        }
                }
        }

    /* To restrict duplication of payterms */
    public static boolean doesShippingTermsExist( DBTransaction dbTransaction, String newShippingTerms ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalmethods = 0;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.doesShippingTermsExist" ) );
            pstmt.setString( 1, newShippingTerms );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                totalmethods = rs.getInt( "total" );
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not find ShippingTerms, \"" + newShippingTerms + "\" in the database", sqle );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        if( totalmethods > 0 )
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

